Optimizing Server Performance Using Memory Configuration Options
The memory manager component of Microsoft SQL Server 2005 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 does not release memory below the configured minimum server memory once that threshold is reached. This configuration option can be set to a specific value based on the size and activity of your SQL Server. If you choose to set this value, set it to some reasonable value to ensure that the operating system does not request too much memory from SQL Server, which can affect 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 releases 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 gives 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 32727. Concurrent connections are not the same as login connections. The pool of worker threads for an instance of SQL Server only needs to be large enough to service the number of user connections that are actively executing batches at the same time in that instance. Increasing the number of worker threads beyond the default value may result in negative server performance. For more information, see max worker threads Option.
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 still starts 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 is 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 has 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
Tasks
How to: Set Minimum Query Memory (SQL Server Management Studio)
Concepts
Monitoring Memory Usage
Effects of min and max server memory
Managing Memory for Large Databases