MSDN Troubleshooting Performance Problems in SQL Server 2008 chapter "Memory Bottlenecks"
Memory pressure indicates that a limited amount of memory is available. Identifying when SQL Server runs under memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present.
Detecting Memory Pressures
Memory pressure by itself does not indicate a problem. Memory pressure is a necessary but not a sufficient condition for the server to encounter memory errors later on. Working under memory pressure could be a normal operating condition for the server. However, signs of memory pressure can also indicate that the server is running close to its capacity and the potential for out-of-memory errors exists. In the case of a normally operating server, you can use information about memory pressures as a baseline for determining reasons for out-of-memory conditions later.
Tools for Memory Diagnostics
The following tools and sources of information can be used for memory troubleshooting:
- General system and SQL Server state and memory-specific DMVs.
- The DBCC MEMORYSTATUS command.
- SQL Server ring buffers.
- Performance counters.
- The SQL Server error log, and Windows application and system logs. You can use
Log File Viewer in SQL Server Management Studio to look at Windows and SQL Server error logs in one place in a time-synchronized fashion. Log File Viewer is accessible through Object Explorer. For connected database servers, expand Management, and then click SQL Server Logs. For more information, see Log File Viewer (http://msdn.microsoft.com/en-us/library/dd206996.aspx)in SQL Server 2008 Books Online.
New DMVs in SQL Server 2008
Several new dynamic management views (sometimes known as DMVs) in SQL Server 2008 simplify retrieval of information that can be helpful with memory troubleshooting. In some cases, newly introduced DMVs provide information that was previously available only in DBCC MEMORYSTATUS output. The following list provides a summary of new DMVs for memory troubleshooting:
- sys.dm_os_memory_brokers provides information about memory allocations using the internal SQL Server memory manager. The information provided can be useful in determining very large memory consumers.
- sys.dm_os_memory_nodes and sys.dm_os_memory_node_access_stats provide information about physical non-uniform memory access (NUMA) memory nodes and node access statistics grouped by the type of the page. (sys.dm_os_memory_node_access_stats is populated under dynamic trace flag 842 due to its performance impact.)
- sys.dm_os_nodes provides information about CPU node configuration for SQL Server. This DMV also reflects software NUMA (soft-NUMA) configuration.
- sys.dm_os_process_memory provides overview information about SQL Server memory usage.
- sys.dm_os_sys_memory provides overview information about the system memory usage.
- sys.dm_resource_governor_configuration, sys.dm_resource_governor_resource_pools, and sys.dm_resource_governor_workload_groups provide information about the state of the Resource Governor feature of SQL Server 2008.
Some of the configuration parameters of Resource Governor affect how SQL Server allocates memory; you should check these parameters during memory troubleshooting.
total_physical_memory_kb / 1024 as phys_mem_mb,
available_physical_memory_kb / 1024 as avail_phys_mem_mb,
system_cache_kb /1024 as sys_cache_mb,
(kernel_paged_pool_kb+kernel_nonpaged_pool_kb) / 1024
total_page_file_kb / 1024 as total_page_file_mb,
available_page_file_kb / 1024 as available_page_file_mb,