Optimizing Server Performance Using Memory Configuration Options
The memory manager component of Microsoft SQL Server 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 value for max worker threads, 0, allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems; however, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance. For more information, see max worker threads Option.
Note
The default setting of max worker threads in SQL Server 2000 was 255. Upgrading an instance of the SQL Server 2000 Database Engine to a newer version retains the configuration value for max worker threads. When upgrading, we recommend changing the new instance max worker threads value to 0, to allow the Database Engine to calculate the optimal number of threads.
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.