Optimizing Server Performance Using Memory Configuration Options

Optimizing SQL Database Performance

Optimizing Database Performance

Optimizing Server Performance Using Memory Configuration Options

The memory manager component of Microsoft® SQL Server™ 2000 eliminates the need for manual management of the memory available to SQL Server. When SQL Server starts, it dynamically determines how much memory to allocate based on how much memory the operating system and other applications are currently using. As the load on the computer and SQL Server changes, so does the memory allocated. For more information, see Memory Architecture.

The following server configuration options can be used to configure memory usage and affect server performance:

  • min server memory
  • max server memory

  • max worker threads

  • index create memory

  • min memory per query

The min server memory server configuration option can be used to ensure that SQL Server starts with at least the minimum amount of allocated memory and does not release memory below this value. This configuration option can be set to a specific value based on the size and activity of your SQL Server. Always set the min server memory server configuration option to some reasonable value to ensure that the operating system does not request too much memory from SQL Server, affecting SQL Server performance.

The max server memory server configuration option can be used to specify the maximum amount of memory SQL Server can allocate when it starts and while it runs. This configuration option can be set to a specific value if you know there are multiple applications running at the same time as SQL Server and you want to guarantee that these applications have sufficient memory to run. If these other applications, such as Web or e-mail servers, request memory only as needed, then do not set the max server memory server configuration option, because SQL Server will release memory to them as needed. However, applications often use whatever memory is available when they start and do not request more if needed. If an application that behaves in this manner runs on the same computer at the same time as SQL Server, set the max server memory server configuration option to a value that guarantees that the memory required by the application is not allocated by SQL Server.

Do not set min server memory and max server memory server configuration options to the same value, thereby fixing the amount of memory allocated to SQL Server. Dynamic memory allocation will give you the best overall performance over time. For more information, see Server Memory Options.

The max worker threads server configuration option can be used to specify the number of threads used to support the users connected to SQL Server. The default setting of 255 can be slightly too high for some configurations, depending on the number of concurrent users. Because each worker thread is allocated, even if it is not being used (because there are fewer concurrent connections than allocated worker threads), memory resources that can be better utilized by other operations, such as the buffer cache, can be unused. Generally, this configuration value should be set to the number of concurrent connections, but cannot exceed 1,024. For more information, see max worker threads Option.

Note  The max worker threads server configuration option has no effect when SQL Server is running on Microsoft Windows® 95 or Microsoft Windows 98.

The index create memory server configuration option controls the amount of memory used by sort operations during index creation. Creating an index on a production system is usually an infrequently performed task, often scheduled as a job to execute during off-peak time. Therefore, when creating indexes infrequently and during off-peak time, increasing this number can improve the performance of index creation. Keep the min memory per query configuration option at a lower number, however, so the index creation job will still start even if all the requested memory is not available. For more information, see index create memory Option.

The min memory per query server configuration option can be used to specify the minimum amount of memory that will be allocated for the execution of a query. When there are many queries executing concurrently in a system, increasing the value of the min memory per query can help improve the performance of memory-intensive queries, such as substantial sort and hash operations. However, do not set the min memory per query server configuration option too high, especially on very busy systems, because the query will have to wait until it can secure the minimum memory requested or until the value specified in the query wait server configuration option is exceeded. If more memory is available than the specified minimum value required to execute the query, the query is allowed to make use of the additional memory, provided that the memory can be used effectively by the query. For more information, see min memory per query Option and query wait Option.

See Also

Monitoring Memory Usage