Memory Tuning

Memory tuning involves the detection of memory-constrained operations. The following memory bottleneck-monitoring guidelines will aid in determining such problems.

Symptoms and Actions

Symptom: If the SQLServer: Cache Hit Ratio is less than 80 percent, memory may be a bottleneck.
Note Be advised that the SQLServer: Cache Hit Ratio in Performance Monitor is an average calculated since boot time, not the value over the last n seconds. Therefore, if many data points accumulate, the graph will only sluggishly change to varying conditions.

Action: Either allocate more memory to SQL Server or increase the amount of system memory.
Symptom: If the SQLServer: I/O - Lazy Writes/sec counter indicates a great deal of activity over a long interval of time, then a memory bottleneck may be indicated. Typically, this counter should be zero until the LRUthreshold (default 3 percent) is reached with respect to free buffer pages. However, frequent activity may indicate not enough memory is available for data-page caching.
Action: Compare the SQLServer: Cache - Number of Free Buffers value against the LRUthreshold value. This value is derived by obtaining the total number of buffers allocated by the DBCC MEMUSAGE statement and multiplying this number by the LRUthreshold percentage. If the number of free buffers is close to the derived value then either allocate more memory to SQL Server or increase the amount of system memory.
Symptom: If Memory: Page Faults/sec is consistently high (other than during SQL Server initialization), then not enough memory is dedicated to Windows NT or possibly the system as a whole.
Action: Increase the system memory or increase the memory dedicated to Windows NT by decreasing the memory allocated to SQL Server or other processes. You can also eliminate noncritical processes because these also use memory resources.
Symptom: If SQLServer: I/O - Page Reads/sec is consistently high, it may indicate a memory bottleneck. Typically, if enough memory is available for database activities during normal operations, this counter will generally reach an equilibrium point. However, if this counter remains high over a long time interval, lack of sufficient memory may be causing excessive SQL Server page faults. This may be due to a less than adequate data cache, poor index design, or poor database design.
Action: Increase the memory allocated to SQL Server or decrease the procedure cache percentage, thereby increasing the data cache. If indexes are not being used, design intelligent indexes. If database tables are too wide (resulting in fewer data rows per data page), redesign the tables to be narrower.
Symptom: If SQLServer: Cache - Avg. Free Page Scan or SQLServer: Cache - Max. Free Page Scan are over 10, a memory bottleneck may be indicated due to excessive buffer scanning while searching for a free page.
Action: Increase the data cache size or the frequency of checkpoints. Checkpoints can be increased by decreasing the recovery interval value or by manual execution.

General Actions for Memory Tuning

Check the amount of memory dedicated to SQL Server by using the SQL Server configuration options. Follow the memory allocation guidelines previously discussed, increasing memory appropriately. Allocate at least 16 MB to Windows NT.
Note Although the memory allocation for keeping tempdb in RAM is not part of the memory configuration value, it may reduce the system memory available for the data and procedure cache.
Make sure the Pagefile.sys file is of adequate size. However, it should not be growing in size; this would indicate that virtual memory requirements are exceeding capacity. On a dedicated SQL Server system, there should be virtually no paging as long as Windows NT has been allotted sufficient memory (at least 16 MB).
If you make extensive use of extended stored procedures, you should unload the DLL after execution by using the DBCC dllname(FREE) statement. This releases the memory used by the DLL.