SQL Server 2005 memory configuration gotchas
This may be last post in Performance Tuning & Security blog section here for this year!! Most of this year in the forums & newsgroups I have seen a common question asking about memory configuration within SQL Server 2005.
As you may be aware that SQL Server 2005 (onwards) the memory allocation is set to DYNAMIC, that means the available memory on the server will be allocated to SQL Server and released whenever a windows service requests for such a memory.
This includes when AWE is enabled. AWE is applicable on 32 bit operating systems AWE related blogs and where the API allows applications to access more than 4GB of RAM. Only the main exception to this feature will be when running SQL Server 2005 on operating system windows 2000 32 bit operating system with AWE enabled; in this case memory allocation is static. AWE support is only available for STANDARD, ENTERPRISE and DEVELOPER editions of SQL Server.
As referred the dynamic memory management is purely for default memory setting that is set on the operating system. At start-up SQL Server only acquires the memory it needs at that point, as users connect and workload hits the server SQL Server will acquire more memory as necessary. SQL Server can also de-allocate memory freeing it for other applications to use however if no other application is requesting memory SQL Server will maintain its memory pool even if there are unused pages. For an effective usage of this pool is dependant on how many application services are installed on the server where this SQL Server instance is located and memory will de-allocated if needed by another process.
As per the configuration the virtual memory space is set on the SQL Server engine too that is specific to less than the physical memory space. This is a very important flavour that prevents excessive paging, some minimal paging is normal. In order to maintain the largest memory available while preventing SQL Server pages from swapping to the page file on disk. This is the reason it is suggested to keep the PAGEFILE on a different drive to SQL Server binaries or data files.
So by default leave the memory settings to DYNAMIC which is a best practice (explained why it is below) too and only if it is required after a thorough testing on the like-to-like production server it is better to allocate min and max memory settings. TO go into further discussion of when and where this static memory setting is required when you have SQL Server 2005 32 bit, Enterprise, Standard or Developer is run on Windows 2000 32 bit operating system with AWE enabled. Obviously if you keep both Min & Max settings to 0, which is an unadvisable setting to make on a production server without testing the outcome. So the setting of Static memory usage if the memory cap is reached by the SQL Server it will not release the memory to Operating System, where you will see excessive paging and spikes in the CPU while other applications on the same server is hungry for memory. That is the reason it is best to leave it dynamic where the system itself manages effectively, as the Windows operating system from 2000 to 2003 is far better.
Here we should talk about AWE-enabled setting too where now a days having more 2GB physical memory is quite common on a default server configuration boxes. This leads to the KBA - How to configure SQL Server to use more than 2 GB of physical memory which talks about setting up the server, that will give advanced options such as using SP_CONFIGURE with SHOW ADVANCED OPTIONS:
Sp_configure “show advanced options”, 1;
RECONFIGURE;
To enable the AWE Enabled setting you must the awe-enabled option to 1 (1 = enabled, 0 = disabled, 0 is the default)
SP_CONFIGURE “awe enabled”, 1;
I think this is where missed by most of the DBAs or SQL Server users that running above SP_CONFIGURE statement will have new settings enabled. IN fact no, you must restart SQL Server services for these AWE enabled option to take affect. In this case you must leave the default settings which is dynamic to enable the SQL Server services to utilize as much as memory as it needs. I wouldn't suggest to set min and max memory settings unless you have thoroughly tested your applicaiton.
Further when you setup the minimum memory settings, say if the option is set to 256 MB by using:
SP_CONFIGURE “min server memory”, 256;
RECONFIGURE;
So this option will set the defined memory to use that will stop other applications to acquire all the memory. Similarly, for the max memory setting:
SP_CONFIGURE “Max Server Memory”, 1024;
RECONFIGURE;
Both of these settings will give the SQL Server engine will acquire the defined amount of memory to use that will prevent usage of that max memory setting by other services/applications to acuire or that memory is available for other applications and they don’t have to wait for SQL Server to release it. The discount in setting up the min and max memory setting will not need any restart of SQL Server services.
So next time when you think about setting up the memory management for SQL Server think about AWE and version of operating system used.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.