T-SQL – Finding Memory Utilization Information
Memory is one the most important factor affecting SQL Server performance.
As an administrator, you should be monitoring the memory regularly. When Microsoft SQL Server runs out of memory, it will use virtual memory: i.e. disk paging, which will grind SQL Server to a halt.
You can keep track of the memory with dbcc memorystatus command.
Following is the simple syntax to use it:
Note: You will get multiple outputs with different headings, these are explained below.
- Memory Manager: shows overall memory consumption by SQL Server
- Memory node Id: usage for each memory node: summary of memory usage
- MEMORYCLERK_SQLGENERAL: aggregate for each memory node: aggregate memory
- Buffer distribution: the distribution of 8-kilobyte (KB) buffers in the buffer pool
- Buffer Counts: Buffer pool details from sys.dm_os_buffer_descriptors
- Procedure cache: Size and type
- Global memory objects: size of the global memory objects
- Query memory objects: snapshot of the query memory usage: the workspace memory
- Optimization Queue: summary of the users who are trying to optimize queries at the same time
- MEMORYBROKER_FOR_CACHE: control cached memory, stolen memory, and reserved memory