Investigating Memory-Related Problems

SQL Server performance is closely related to the availability and performance of sufficient memory. SQL Server configuration-related memory settings include the following:  

➤ sp_configure 

 ➤ Min/max server memory  

➤ AWE Enabled 

 ➤ Min memory per query  

 

➤ Windows  

➤ /3GB, /USERVA, /PAE (in 32-bit environments) 

 ➤ Lock Pages in Memory privilege  

 

Typically, using the Windows Task Manager doesn’t provide the best measure of the memory consumed by SQL Server. Using PerfMon is a more reliable method of measuring memory consumption, since this includes all types of memory allocation that can be made by SQL Server. 

 

Types of Memory Pressure  

SQL Server can suffer from internal or external memory pressure, and understanding how to identify and troubleshoot each will enable more targeted troubleshooting. External memory pressure occurs most often when SQL Server is running on a shared computer and several processes are competing for memory. In this situation, Resource Monitor within SQL Server Operating System (SQLOS) receives a signal from Windows to request that SQL Server reduce its committed memory. This causes SQL Server to recalculate its target commit level, and reduce it if necessary.  

Internal memory pressure occurs when multiple SQL Server resources compete with each other for memory. This typically causes SQL Server to shrink the data cache, which can impact server performance. Use the DBCC MEMORYSTATUS command to gain visibility of SQL Server memory consumption. 

 

 

Memory Performance Counters  

• Memory Available Mbytes Amount of free physical memory in MB; values below 100MB could indicate external memory pressure or a max server memory setting that’s too high. <100MB 

• Memory Pages/sec A high value doesn’t necessarily mean a problem; review this counter if you suspect external memory pressure and always consider in the context of other memory counters. <500 

• Memory Free System Page Table Entries Page Table Entries are most likely to become depleted (and therefore a bottleneck) on x86 servers, particularly where /3GB or /USERVA switches are used. <5000 

• Paging File % Usage, % Usage Peak Generally, workload increases the demand for virtual address pace (VAS), which increases the demand for Page File. Heavy reliance on page file use is usually an indication of memory problems. The threshold will depend on the size of your pagefile. See Chapter 3 for sizing details. >70%  

• MSSQL Buff er Manager Page Life Expectancy Duration, in seconds, that a data page resides in the buffer pool. A server with sufficient memory has high page life expectancy. Watch the trend of this counter over time. Overall it should stay stable or trend higher. Frequent dips of the value can indicate memory pressure.  <300 seconds 

• MSSQL Buff er Manager Buff er Cache Hit Ratio Percent of page requests satisfied by data pages from the buff er pool. Page Life Expectancy is a better overall indicator of buff er pool health. <98% 

• MSSQL Buff er Manager Lazy Writes/ sec Number of times per second SQL Server relocates dirty pages from buff er pool (memory) to disk >20