How to analyze 'dbcc memorystatus' result in SQL Server 2008

Question

Currently I am facing a SQL memory pressure issue. i have run dbcc memorystatus, here is part of my result:

Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              23617160
VM Committed                             14818444
Locked Pages Allocated                   0
Reserved Memory                          1024
Reserved Memory In Use                   0


Memory node Id = 0                       KB
---------------------------------------- -----------
VM Reserved                              23613512
VM Committed                             14814908
Locked Pages Allocated                   0
MultiPage Allocator                      387400
SinglePage Allocator                     3265000


MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
---------------------------------------- -----------
VM Reserved                              16809984
VM Committed                             14184208
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     0
MultiPage Allocator                      408

MEMORYCLERK_SQLCLR (node 0)              KB
---------------------------------------- -----------
VM Reserved                              6311612
VM Committed                             141616
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     1456
MultiPage Allocator                      20144

CACHESTORE_SQLCP (node 0)                KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     3101784
MultiPage Allocator                      300328

Buffer Pool                              Value
---------------------------------------- -----------
Committed                                1742946
Target                                   1742946
Database                                 1333883
Dirty                                    940
In IO                                    1
Latched                                  18
Free                                     89
Stolen                                   408974
Reserved                                 2080
Visible                                  1742946
Stolen Potential                         1579938
Limiting Factor                          13
Last OOM Factor                          0
Page Life Expectancy                     5463

Process/System Counts                    Value
---------------------------------------- --------------------
Available Physical Memory                258572288
Available Virtual Memory                 8771398631424
Available Paging File                    16030617600
Working Set                              15225597952
Percent of Committed Memory in WS        100
Page Faults                              305556823
System physical memory high              1
System physical memory low               0
Process physical memory low              0
Process virtual memory low               0

Procedure Cache                          Value
---------------------------------------- -----------
TotalProcs                               11382
TotalPages                               430160
InUsePages                               28

Can you lead me to analyze this result ?

Is it a lot execute plan have been cached causing the memory issue or other reasons?

Answer 1

This is a bit late, but perhaps it will help someone else who reads this. From seeing Available Virtual Memory of 8 TB, I can tell this is a 64 bit system - along with the absence of any references to AWE allocation.

As Lette points out, the OS itself only has 256 MB of Available Physical Memory - but that's just what's remaining, not the total amount installed. SQL will try to use as much physical memory that's installed as possible for performance; accessing memory is by far faster than moving a disk head.

Going by VM Committed, SQL is using 14.1 GB of physical memory going by VM Committed - I'll guess that 16 GB total of physical memory is present, accounting for OS needs, available physical memory, and 16 being a good round number.

Memory pressure is coming from two primary areas: SQL buffer pool, and SQL Plan Cache.

SQL Buffer Pool

About 13.5 GB of memory is benig used for the buffer pool. Not atypical for SQL; it will try to use as much memory as it can.

SQL Plan Cache:

Aaccording to 11,382 ad-hoc queries query plans are cached. However, only 28 plans are in use - less than 1%. If we map this back to CACHESTORE_SQLCP, we see an interesting story - no memory is currently being used for these plans at this time, but I think at one point it had consuming 3.24 GB of memory. I must admit that I'm less sure of this, and would certainly appreciate a 2nd opinion on seeing 0 for VM Commmitted but values present for the allocators.

Summary Since you're running SQL 2008, consider enabling optimizing for ad hoc query plans. This will help quite a bit with memory pressure if your workloads are primarily ad hoc.

Reference

Answer 2

Here's a guess:

One thing that strikes me is that you have a Working Set of 15 GB while the Available Physical Memory is only 258 MB. I believe you should make more memory available to Sql Server. (Whether that's just moving a slider a little more to the right, and/or installing more RAM, I couldn't know.)

Answer 3

The docs for DBCC MEMORYSTATUS() are here: http://support.microsoft.com/kb/907877

They're not terribly verbose - but will, at least, give you an idea of what you're looking at.