How to parse "dbcc memorystatus" in SQL Server 2008

Question

Currently, I am facing SQL memory shortage problem. I ran 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?

How much fulfillment of the execution plan that caused the memory problem or other reasons?

Answer 1

It's a bit late, but maybe it will help someone else who reads it. From viewing the Available Virtual Memory from 8 TB I can say that this is a 64-bit system - along with the lack of links to the AWE distribution.

As Lette points out, the OS itself has only 256 MB of Available Physical Memory , but that's what remains, not the total. SQL will try to use as much physical memory as possible for performance; access to memory is much faster than moving the disk head.

Starting with VM Committed , SQL uses 14.1 GB physical memory going to VM Committed . I assume that there is a total physical memory of 16 GB, which corresponds to the needs of the operating system, available physical memory and 16 is a good round number.

The memory voltage comes from two main areas: the SQL buffer pool and the SQL Plan cache.

SQL Buffer Pool

About 13.5 GB of memory is used for the buffer pool. Non-standard for SQL; he will try to use as much memory as he can.

SQL plan cache:

In accordance with 11,382 queries, query requests are cached. However, only 28 plans are used - less than 1%. If we compare this with CACHESTORE_SQLCP, we see an interesting story - currently, these plans do not use memory, but I think that at some point it consumed 3.24 GB memory. I must admit that I am less confident in this and will certainly appreciate the second opinion that 0 for VM Commmitted, but values ​​for distributors.

Summary Since you are using SQL 2008, consider turning on optimization for custom query plans . This will help slightly reduce memory pressure if your workloads are mostly done by ad hoc.

Link

Answer 2

Here's an assumption:

One thing that amazes me is that you have a Working Set of 15 GB , and Available Physical Memory is only 258 MB . I believe that you should make more memory available for Sql Server. (Is this just moving the slider a little to the right and / or installing more RAM, I could not know.)

Answer 3

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

They are not terribly detailed - but at least they will give you an idea of ​​what you are watching.