The memory management component of Microsoft® SQL ServerTM 2000 eliminates the need for manual management of the memory available to SQL Server. SQL Server dynamically determines the amount of memory that should be allocated at startup based on the amount of memory currently being used by the operating system and other applications. When the load on the computer and SQL Server changes, the allocated memory also changes. 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 min server memory server configuration options can be used to ensure that SQL Server is at least minimal The amount of allocated memory is started, and memory is not released when the memory is below this value. This configuration option can be set to a specific value based on the size and activity of SQL Server. Always set the min server memory server configuration option to a reasonable value to ensure that the operating system does not request too much memory from SQL Server to affect SQL Server performance. The max server memory server configuration option can be used to specify the maximum amount of memory that SQL Server can allocate when SQL Server starts and runs. If you know that there are multiple applications running concurrently with SQL Server and you want to ensure that these applications have enough memory to run, you can set this configuration option to a specific value. If these other applications, such as web servers or email servers, only request memory as needed, SQL Server will free them as needed, so do not set the max server memory server configuration option. However, applications typically use available memory without a pseudo-selection at startup, and do not request more memory if needed. If an application with this behavior is running on the same computer as SQL Server, set the max server memory server configuration option to a specific value to ensure that the memory required by the application is not allocated by SQL Server. Do not set the min server memory and max server memory server configuration options to the same value, which will result in a fixed amount of memory allocated to SQL Server. Dynamic memory allocation provides the best overall performance over time. The max worker threads server configuration option can be used to specify the number of threads that support user connections to SQL Server. 255 This default setting may be slightly higher for some configurations, depending on the number of concurrent users. Since each worker thread is allocated, even if the thread is not being used (because the concurrent connection is less than the assigned worker thread), memory resources that can be better utilized by other operations (such as cache) may be unused. In general, this configuration value should be set to the number of concurrent connections, but it cannot exceed 1,024.
Notes
When SQL Server is running on Microsoft Windows® 95 or Microsoft Windows 98, the Maximum Worker Thread Server configuration option does not work. Index create memory The server configuration option controls the amount of memory used by the sort operation when creating an index. Creating an index on a production system is often an infrequently performed task, usually scheduled for jobs that are executed during off-peak hours. Therefore, when indexes are not often created and increased during off-peak hours, the performance of index creation can be improved. However, it's a good idea to keep the minmemoryperquery configuration option at a lower value so that the index creation job can start even if all requested memory is not available. The minmemoryperquery server configuration option can be used to specify the minimum amount of memory allocated for query execution. Increasing the value of minmemoryperquery helps improve the performance of queries that consume large amounts of memory, such as large sorts and hashes, when there are many queries executing concurrently in the system. However, do not set the minmemoryperquery server configuration option too high, especially on very busy systems, because the query will have to wait until it can ensure that it has the minimum memory occupied by the request, or waits longer than the value specified in the query wait server configuration option. If the available memory is more than the specified minimum memory required to execute the query, then as long as the query can effectively utilize the extra memory, you can use the extra memory.