Database Reference
In-Depth Information
Dynamic Management Objects
There are a large number of memory-related DMOs within SQL Server. Several of them have been updated with SQL
Server 2014, and some new ones have been added. Reviewing all of them is outside the scope of this topic. There are
three that are the most frequently used when determining whether you have memory bottlenecks within SQL Server.
There are also another two that are useful when you need to monitor your in-memory OLTP memory usage.
Sys.dm_os_memory_brokers
While most of the memory within SQL Server is allocated to the buffer cache, there are a number of processes within
SQL Server that also can, and will, consume memory. These processes expose their memory allocations through this
DMO. You can use this to see what processes might be taking resources away from the buffer cache in the event you
have other indications of a memory bottleneck.
Sys.dm_os_memory_clerks
A memory clerk is the process that allocates memory within SQL Server. Looking at what these processes are up to
allows you to understand whether there are internal memory allocation issues going on within SQL Server that might
rob the procedure cache of needed memory. If the Performance Monitor counter for Private Bytes is high, you can
determine which parts of the system are being consumed through the DMV.
If you have a database using in-memory OLTP storage, you can use sys.dm_db_xtp_table_memory_stats to look
at the individual database objects. But if you want to look at the allocations of these objects across the entire instance,
you'll need to use sys.dm_os_memory_clerks.
Sys.dm_os_ring_buffers
This DMV is not documented within Books Online, so it is subject to change or removal. It changed between SQL
Server 2008R2 and SQL Server 2012. The queries I normally run against it still seem to work for SQL Server 2014,
but you can't count on that. This DMV outputs as XML. You can usually read the output by eye, but you may need to
implement XQuery to get really sophisticated reads from the ring buffers.
A ring buffer is nothing more than a recorded response to a notification. Ring buffers are kept within this DMV,
and accessing sys.dm_os_ring_buffers allows you to see things changing within your memory. Table
2-2
describes the
main ring buffers associated with memory.
