How to Identify Memory related performance problem in Microsoft SQL Server

There are many reasons for memory related performance problems on a MS SQL Server instance, there can be either memory pressure from other applications, limit in physical or virtual memory or inside the SQL Server. We have many built-in tools which can be used to know the root cause.

DBCC MEMORYSTATUS command

We can use the DBCC MEMORYSTATUS command to check for any abnormal memory problem in SQL Server. Run the

DBCC MEMORYSTATUS

Command and scroll down to the Buffer Counts section, look for the Target value. It shows the number of 8-KB pages which can be committed without causing paging. A drop in the number of target pages might indicate response to an external physical memory pressure.

If the Committed amount is above Target, continue investigating the largest memory consumers inside SQL Server. When the server is not loaded, Target normally exceeds Committed and the value of the Process: Private Bytes performance counter.

If the Target value is low, but the server Process: Private Bytes is high, you might be facing internal SQL memory problems with components that use memory from outside of the buffer pool. Such components include linked servers, COM objects, extended stored procedures, SQL CLR, etc. If the Target value is low but its value is close to the total memory used by SQL Server, than you should check whether your SQL Server received a sufficient amount of memory from the system. Also you can check the server memory configuration parameters.

You can compare the Target count against the max server memory values if it is set. Later option limits the maximum memory consumption of the buffer pool. Therefore the Target value cannot exceed this value. Also the low Target count can indicate problems: in case it is less than the min server memory setting, you should suspect external virtual memory pressure.

Also check the Stolen Pages count in DBCC MEMORYSTATUS output. A high percentage (>70%) of Stolen Pages compared to Target can be a sign of internal memory pressure.

Further reading on Microsoft Support page:

• How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

Resource Governor

The Resource Governor in SQL Server 2008 Enterprise edition allows you to fine tune SQL Server memory allocation strategies, but incorrect settings can be a cause for out-of-memory errors. The following DMVs can provide information about the Resource Governor feature of SQL Server 2008:

sys.dm_resource_governor_workload_groups, sys.dm_resource_governor_configuration and sys.dm_resource_governor_resource_pools

SQL Server Profiler

SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. It shows how SQL Server resolves queries internally. This allows administrators to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server and how the server accesses the database or cube to return result sets.

Additional information can be found in the articles

Working with SQL Server Profiler Trace Files andCreating a Trace Template in SQL Server Profiler.

SQL Server ring buffers

Another source of diagnostic memory information is the sys.dm_os_ring_buffers DMV. Each ring buffer records the last number of notifications. You can query the ring buffer event counts using the following code:

SELECT ring_buffer_type, COUNT(*) AS [Events] FROM sys.dm_os_ring_buffers GROUP BY ring_buffer_type ORDER BY ring_buffer_type

Here is a list of ring buffers of interest:

• RING_BUFFER_BUFFER_POOL: This ring buffer contains records of buffer pool failures.
• RING_BUFFER_RESOURCE_MONITOR: This ring buffer captures every memory state change by using resource monitor notifications.
• RING_BUFFER_SCHEDULER_MONITOR: Stores information about the overall state of the server. The SystemHealthrecords are created with one minute intervals.
• RING_BUFFER_MEMORY_BROKER: This ring buffer contains memory notifications for the Resource Governor Resource pool.
• RING_BUFFER_OOM: This ring buffer contains records indicating out-of-memory conditions.

Dynamic Management Views

You can use the

sys.dm_os_memory_clerks

Dynamic management view (DMV) to get detailed information about memory allocation by the server components in SQL Server 2005 and 2008. You can get additional information about the caches by joining with the sys.dm_os_cache_counters (Please note that the amount of pages is NULL for USERSTORE entries):

SELECT DISTINCT SDMC.cache_address, SDMC.name, SDMC.type, SDMC.single_pages_kb, SDMC.multi_pages_kb, SDMC.single_pages_in_use_kb, SDMC.multi_pages_in_use_kb, SDMC.entries_count, SDMC.entries_in_use_count, SDMCCH.removed_all_rounds_count, SDMCCH.removed_last_round_count FROM sys.dm_os_memory_cache_counters AS SDMC JOIN sys.dm_os_memory_cache_clock_hands SDMCCH ON (SDMC.cache_address = SDMCCH.cache_address)

You can also use the following DMVs for memory troubleshooting both in SQL Server 2005 and 2008:

• sys.dm_exec_requests
• sys.dm_exec_query_memory_grants
• sys.dm_exec_query_resource_semaphores
• sys.dm_exec_sessions
• sys.dm_exec_cached_plans
• sys.dm_os_memory_cache_entries

Performance Monitor

Performance Monitor is part of the Microsoft Management Console, you can find it by navigating to Start Menu -> Administrative Tools Group. Additional information can be found in these articles:

• Setting up Performance Monitor to always collect SQL Server performance statistics
• SQL Server Database Specific Performance Counters