Memory has a dramatic impact on SQL Server Performance. Fortunately, in SQL Server you can either use DMVs, , Extended Events, sp_server_diagnostics system procedure or SQL Profiler to server memory usage and track down root cause of SQL Server memory bottlenecks. In this article, I will provide you high-level overview of these memory-related tools.
Dynamic management views
Dynamic management views were first introduced with SQL Server 2005. They provide a wealth of information on server and database state, which is useful in monitoring overall SQL Server health, identify the root cause of SQL Server performance bottlenecks, and tune SQL Server instance or database performance. The following are the list of dynamic management views that are available in SQL Server 2008R2 and up which you can use to obtain SQL Server memory usage information, for example, how much memory is available to SQL Server instance, or how well SQL Server instance is utilizing the available memory.
sys.dm_os_sys_info: Returns computer resources usage information
sys.dm_os_memory_cache_counters: Returns run-time information about the cache entries allocated, their use, and the source of memory for the cache entries
sys.dm_os_memory_nodes: Returns information about the memory nodes
sys.dm_os_memory_cache_entries: Returns original and current cost of any cache entry
sys.dm_os_memory_cache_hash_tables: Returns a row for each active cache in the instance of SQL Server
sys.dm_os_memory_cache_clock_hands: Returns the status of each hand for a specific cache clock
sys.dm_os_memory_clerks: Returns detailed information about all memory clerks, which SQL Server is currently consuming
sys.dm_os_memory_objects: Returns information about the memory objects that are currently allocated by the instance of SQL Server
sys.dm_os_performance_counters: Returns SQL Server specific performance counter information
sys.dm_os_ring_buffers: Returns detailed information about system health. The information returned is useful to troubleshoot memory pressure.
In addition to this, SQL Server 2014 also has set of dynamic management views that are specific to new for In-Memory OLTP engine. You can use these dynamic management views to monitor the memory-related usage information that is specific to new In-Memory OLTP engine. For more information, refer to the Memory-Optimized Table Dynamic Management Views (Transact-SQL) article at http://msdn.microsoft.com/en-us/library/dn133203.aspx.
DBCC MEMORYSTATUS Command
MEMORYSTATUS is an undocumented DBCC command that provides a snapshot of the current memory status of Microsoft SQL Server. This memory usage information is not only useful for you to quickly identify the root cause of the memory pressure on SQL Server, but also helps you analyze how well SQL Server is using the allocated memory. Some of the information included in DBCC MEMORYSTATUS outputs is still not available through dynamic management views, which I mentioned earlier. That is why; Microsoft Customer Support Services team and experienced database administrators still rely on this undocumented DBCC command output to quickly troubleshoot Microsoft SQL Server memory consumption issues.
The DBCC MEMORYSTATUS describes the distribution of 8k buffers among various components of SQL Server. It gives you sets of measures that show how memory is currently allocated in SQL Server. This information is particularly useful, if you are troubleshooting issues that are related to SQL Server Memory consumption. Its output contains the following sections:
Output contains seven sections:
Shows distribution of committed memory
Shows size and makeup of buffer pool
Describes makeup of the procedure cache
Shows distribution of Dynamic Memory Usage
Shows distribution of various global memory usage
Displays query memory grant information
Shows concurrent query optimization status
Displays the memory management info like VM Reserved, VM Committed, AWE Allocated, Reserved Memory, Reserved Memory In Use.
How the SQL Server buffer cache is divided up, including buffer activity.
Information about Memory Manager, Clerks, Buffer Manager, and number of other SQL Server processes that consume SQL Server memory
Syntax
DBCC MEMORYSTATUS
Sample Output
Full article...
