The default value for SQL Server 2008 Maximum Server Memory setting is 2,147,483,647 MB (or 2.1 petabytes!). Therefore, by default, SQL Server will use all available memory for its own use:
If you don’t lower this setting, you will reduce the memory available for other services such as Integration Services (SSIS), Analysis Services (SSAS), Reporting Services (SSIS) as well as other Windows services (you should also disable the services that you do not use).
I have run into cases where SQL Server 2008 had difficulties handling memory pressure and would throw the infamous “There is insufficient system memory to run this query” error.
Depending on the amount of total memory of your production server, and if you don’t use other memory-intensive services, you should at least reserve 1-2 Go to the Windows operating system (for example, by specifying 14000 MB on a 16 Go machine). If your server is 64-bit, you will be able to easily verify that the sqlserv.exe process in Windows Task Manager will never be way higher than the value you specified (it can go a little higher). If you have a 32-bit version of SQL Server, the sqlserv.exe process will be limited to 2 or 3 Go depending on your server configuration.
To allow SQL Server to use more than 3 Go of memory on 32-bit systems, you have to configure AWE memory allocation. Thereafter, you can verify the memory utilization by issuing the DBCC MEMORYSTATUS command or use SQL Server counters in Performance Monitor (here Windows Task Manager will not report the correct memory utilization). That being said, you should not enable AWE if your 32-bit server doesn’t have more than 4 Go of memory. Also, keep in mind that AWE setting will be ignored on 64-bit setups.
It goes without saying that if you are running multiple SQL Server instances or other memory-intensive processes such as Analyses Services on the same server, you should carefully configure each services’ memory settings. For example, Analysis Services provide the same memory control mechanism with Memory \ TotalMemoryLimit server property.
RSS