Question:
We are in the process of fine tuning our primary site. We are looking at changing some setting in SQL 2005. Is anyone using AWE to allocate memory with SCCM? Also is there a recommendation to use a maximum server memory? We have 4 gb of memory on our Primary Site Server with the database on the same box. Should we up our maximum server memory in SQL to use more then 2 gb?
~~~~
[Steve] When running Microsoft SQL Server 2005 on a server (let’s say Windows Server 2003) with SCCM/SMS, do not let SQL Server dynamically use all available memory. There are circumstances that may allow SQL to consume all available memory. This activity in turn will slow the operating system, SMS/SCCM, or both.
The goal of fine tuning memory usage is to balance the amount of memory allocated to SQL, with leaving enough memory that the OS and SMS/SCCM can function without using the page file. I recommend using AWE, with Dynamic memory configuration. Set a minimum and maximum amount of server memory. Use SQL Server: DBCC MEMORYSTATUS for memory usage metrics as perfmon is not accurate http://support.microsoft.com/kb/907877/en-us
With 4GB of memory, and as a starting point, consider allocating a maximum of 3GB (dynamic memory) to SQL Server. This allows the remainder of memory to be reserved for the operating system and SMS/SCCM.
If you decide not to use AWE, use performance monitor logging to monitor key memory counters, page file usage, etc over a few days. Make adjustments as needed to SQL Server dynamic memory usage. Here is one example for illustration (3gb server memory)
From SQL Server 2005 Books Online Configuring Memory Options
SQL Server 2005 dynamically allocates AWE-mapped memory when running with any of the Windows Server 2003 operating system editions. In other words, the buffer pool can dynamically manage AWE-mapped memory (within the constraints of the min server memory and max server memory options) to balance SQL Server memory use with the overall system requirements. When AWE is enabled, SQL Server 2005 always attempts to use AWE-mapped memory. This applies to all memory configurations, including computers configured to provide applications with less than 3 GB of user mode address space. - We recommend setting AWE as the default memory mode for SQL Server 2005 running under Windows Server 2003. The Hot-Add Memory feature requires AWE to be enabled during SQL Server startup. For information, see Hot Add Memory.
| Note: |
| AWE is not needed and cannot be configured on 64-bit operating systems. |
- Since AWE-mapped memory is supported below 3 GB, you can define the min server memory and max server memory values within the physical memory range, or use the default values for both options.
- You may consider setting max server memory for SQL Server to guarantee additional memory for other applications operating on the computer. Although SQL Server can dynamically release AWE-mapped memory, the current amount of allocated AWE-mapped memory cannot be swapped out to the page file.
Update July 2011
Please see updated information for SQL Server 2008:
SQL Server memory and performance tuning for ConfigMgr