I will be writing
this article in four parts because this topic is huge and amount of information
can be overwhelming for readers and for me also. So we will be exploring it
step by step.
1.
DBCC
MemoryStatus
2.
Using DMV
3.
Alert and
perfmon counters
As you may remember from my
previous post of this Topic (Introduction- Detecting and Monitoring Memory Pressure), if not I would recommend that you take a look. In this post we will be
discussing the DBCC MemoryStatus command. This command gives a snapshot of the
memory status of Sql Server and it can be used for troubleshooting memory
related issues or out of memory errors. So this can be used as a diagnostic
tool for Sql Server. Let’s start with Syntax, it is very simple you just type
‘DBCC MemoryStatus’ and Press F5 or run from UI. Since Syntax is pretty simple
but not the Result or Output you get. Yes you get tons of information. Here we
will be focusing on important information.
Process/System counts provide you the
overall memory status and you get an idea about total available memory physical
and virtual, working set etc. When I ran the command I got the following
details. All the memory here is showed in Kb, you can do calculation and
convert it into GB. Actually you don’t to do conversion because you are
familiar about your system resources and numbers here can give an overall idea
about their consumption.
Process/System Counts
|
Value
|
Available Physical Memory
|
5,547,806,720
|
Available Virtual Memory
|
140,724,378,267,648
|
Available Paging File
|
9,124,757,504
|
Working Set
|
369,946,624
|
Percent of Committed
Memory in WS
|
53
|
Page Faults
|
5,317,030
|
System physical memory
high
|
1
|
System physical memory
low
|
0
|
Process physical memory
low
|
0
|
Process virtual memory
low
|
0
|
Memory Manager –
Memory
manager shows the information about overall memory consumed by Sql Server. Here
is the output for my instance.
Memory Manager
|
KB
|
VM Reserved
|
12,441,152
|
VM Committed
|
681,624
|
Locked Pages Allocated
|
0
|
Large Pages Allocated
|
0
|
Emergency Memory
|
1,024
|
Emergency Memory In Use
|
16
|
Target Committed
|
5,681,760
|
Current Committed
|
681,624
|
Pages Allocated
|
468,016
|
Pages Reserved
|
0
|
Pages Free
|
1,584
|
Pages In Use
|
648,080
|
Page Alloc Potential
|
7,320,600
|
NUMA Growth Phase
|
0
|
Last OOM Factor
|
0
|
Last OS Error
|
0
|
Ø VM Reserved – It shows the virtual
address space reserved by SQL Server.
Ø VM Committed – It shows overall
virtual address space committed by Sql Server. Committed virtual address space
has been associated with physical memory.
Ø AWE Committed – It shows amount of
memory that is provided by Address Windowing Extensions (AWE), for a 32 bit Sql
Server it shows the amount of memory and for a 64 bit it shows memory consumed
by locked pages.
Ø Reserved Memory – This shows the
reserved memory for a dedicated administrator connection.
Ø Reserved Memory In Use – It shows the
amount of reserved memory being used.
Summary of memory usage -
This section shows the
summary of memory usage by each memory nodes, output depends on your system
setup if you are using NUMA (non-uniform memory access) then you will be getting same result set for each node. In case of SMP (Symmetric Multi-Processing), you will get single result set.
Memory node Id = 0
|
KB
|
VM Reserved
|
12,441,088
|
VM Committed
|
681,604
|
Locked Pages Allocated
|
0
|
Pages Allocated
|
468,016
|
Pages Free
|
1,584
|
Target Committed
|
5,681,736
|
Current Committed
|
681,608
|
Foreign Committed
|
0
|
Away Committed
|
0
|
Taken Away Committed
|
0
|
Ø Memory node Id: It shows the node ID for which it showing result set.
Ø VM Reserved –
It shows the virtual
address space reserved by threads which belongs to this node.
Ø VM Committed – It shows committed virtual address space by threads.
Ø AWE Allocated – It shows memory
allocated by Address Windowing Extensions and
depends on your system configurations (32bit or 64 bit).
MEMORYCLERK_SQLGENERAL -
It shows aggregate
memory information for each clerk type and for each NUMA node.
MEMORYCLERK_SQLGENERAL
(node 0)
|
KB
|
VM Reserved
|
0
|
VM Committed
|
0
|
Locked Pages Allocated
|
0
|
SM Reserved
|
0
|
SM Committed
|
0
|
Pages Allocated
|
10,576
|
We can use sys.dm_os_memory_clerks DMV for more information.
select
type,
sum(virtual_memory_reserved_kb) as [VM Reserved],
sum(virtual_memory_committed_kb) as [VM Committed],
sum(awe_allocated_kb) as [AWE Allocated],
sum(shared_memory_reserved_kb) as [SM Reserved],
sum(shared_memory_committed_kb) as [SM Committed],
sum(multi_pages_kb) as [MultiPage
Allocator],
sum(single_pages_kb) as [SinlgePage
Allocator]
from
sys.dm_os_memory_clerks
group by type
Buffer distribution -
The next section shows the distribution of
8-kilobyte (KB) buffers in the buffer pool.
Buffer Distribution
|
Buffers
|
Stolen
|
553
|
Free
|
103
|
Cached
|
161
|
Database (clean)
|
1353
|
Database (dirty)
|
38
|
I/O
|
0
|
Latched
|
0
|
Buffer pool details -
We can get more details about Buffer pools from following
DMV –
sys.dm_os_buffer_descriptors
sys.dm_os_memory_clerks
Buffer Pool
|
Value
|
Database
|
3,993
|
Simulated
|
955
|
Target
|
16,384,000
|
Dirty
|
103
|
In IO
|
0
|
Latched
|
0
|
Page Life Expectancy
|
1,551
|
Procedure cache -
The next section
describes the makeup of the procedure cache.
Procedure Cache
|
Value
|
TotalProcs
|
3
|
TotalPages
|
12354
|
InUsePages
|
0
|
Global memory objects-
This section shows the
information about global memory objects
and how much memory they are using.
Global Memory Objects
|
Pages
|
Resource
|
428
|
Locks
|
888
|
XDES
|
46
|
DirtyPageTracking
|
188
|
SETLS
|
2
|
SubpDesc Allocators
|
2
|
SE SchemaManager
|
721
|
SE Column Metadata Cache
|
901
|
SE Column Metadata Cache
Store
|
59
|
SQLCache
|
303
|
Replication
|
2
|
ServerGlobal
|
52
|
XP Global
|
2
|
SortTables
|
3
|
Query memory objects -
This section shows
you snapshot of Query memory objects also called snapshot of the workspace memory. If the query execution cost is small then query
will be moved to Small Query queue. In this way small query doesn’t have to
wait for larger queries to finish first.
Query Memory Objects
(internal)
|
Value
|
Small Query Memory
Objects (internal)
|
Value
|
|
Grants
|
0
|
Grants
|
0
|
|
Waiting
|
0
|
Waiting
|
0
|
|
Available
|
507015
|
Available
|
26685
|
|
Current Max
|
507015
|
Current Max
|
26685
|
|
Future Max
|
507015
|
Future Max
|
26685
|
|
Physical Max
|
507015
|
|||
Next Request
|
0
|
|||
Waiting For
|
0
|
|||
Cost
|
0
|
|||
Timeout
|
0
|
|||
Wait Time
|
0
|
Memory brokers -
This section shows information about memory
brokers that manage reserved memory, stolen memory and cached memory. This
information is used for internal diagnostics that why detailed information is
not available.
MEMORYBROKER_FOR_CACHE
(internal)
|
KB
|
Allocations
|
353,664
|
Rate
|
24
|
Target Allocations
|
4,089,624
|
Future Allocations
|
0
|
Overall
|
4,554,280
|
Last Notification
|
1
|
Optimization Queue -
This section provides summary of the users who
are trying to optimize queries at the same time.
During compilation process, queries are categorized
based on the memory requirement which will be used during compilation process. This controls the compilation of memory
intensive query at the same time, so improves the throughput of smaller queries.
Optimization Queue
(internal)
|
Value
|
Overall Memory
|
4,663,582,720
|
Target Memory
|
3,909,566,464
|
Last Notification
|
1
|
Timeout
|
6
|
Early Termination Factor
|
5
|
Small Gateway (internal)
|
Value
|
Configured Units
|
8
|
Available Units
|
8
|
Acquires
|
0
|
Waiters
|
0
|
Threshold Factor
|
380000
|
Threshold
|
380000
|
Medium Gateway (internal)
|
Value
|
Configured Units
|
2
|
Available Units
|
2
|
Acquires
|
0
|
Waiters
|
0
|
Threshold Factor
|
12
|
Threshold
|
-1
|
Big Gateway (internal)
|
Value
|
Configured Units
|
1
|
Available Units
|
1
|
Acquires
|
0
|
Waiters
|
0
|
Threshold Factor
|
8
|
Threshold
|
-1
|
Procedure cache: Size and type
Procedure Cache
|
Value
|
TotalProcs
|
3
|
TotalPages
|
12,354
|
InUsePages
|
0
|
On most of the points
mentioned above, very less information is made available by the Microsoft. So
basically understanding and utilizing the information depends on our experience
and knowledge about our system.
Please provide your feedback,
if you think something is missing or wrong. I would be glad to know them and
will try to improve it.
