如何在 SQL Server 2008 中分析“dbcc memorystatus”结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2810781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to analyze 'dbcc memorystatus' result in SQL Server 2008
提问by user337390
Currently I am facing a SQL memory pressure issue. i have run dbcc memorystatus, here is part of my result:
目前我正面临 SQL 内存压力问题。我已经跑了dbcc memorystatus，这是我的结果的一部分：
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?
是否有很多执行计划被缓存导致内存问题或其他原因？
回答by JohnW
This is a bit late, but perhaps it will help someone else who reads this.
From seeing Available Virtual Memoryof 8 TB, I can tell this is a 64 bit system - along with the absence of any references to AWE allocation.
这有点晚了，但也许它会帮助阅读本文的其他人。从看到Available Virtual Memory的8 TB，我可以告诉这是64位系统-与缺乏对AWE分配的任何引用的一起。
As Lette points out, the OS itself only has 256 MBof 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.
正如莱特指出，OS本身只具有256 MB的Available Physical Memory-但是这只是安装了什么剩余，而不是总量。SQL 将尝试使用尽可能多的已安装的物理内存来提高性能；访问内存远远快于移动磁盘磁头。
Going by VM Committed, SQL is using 14.1 GBof 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.
顺便说一下VM Committed，SQL 正在使用14.1 GB物理内存VM Committed- 我猜总共有 16 GB 的物理内存，考虑到操作系统需求、可用物理内存，16 是一个很好的整数。
Memory pressure is coming from two primary areas: SQL buffer pool, and SQL Plan Cache.
内存压力来自两个主要方面：SQL 缓冲池和 SQL 计划缓存。
SQL Buffer Pool
SQL 缓冲池
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.
大约 13.5 GB 的内存用于缓冲池。对于 SQL 来说不是非典型的；它将尝试使用尽可能多的内存。
SQL Plan Cache:
SQL 计划缓存：
Aaccording to 11,382ad-hoc queries query plans are cached. However, only 28plans 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 GBof 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.
根据11,382即席查询查询计划被缓存。但是，只有28计划在使用 - 不到 1%。如果我们将其映射回 CACHESTORE_SQLCP，我们会看到一个有趣的故事 - 目前没有内存用于这些计划，但我认为它曾经消耗3.24 GB了内存。我必须承认，我不太确定这一点，并且当然会很感激看到 0 表示 VM 已提交但分配器存在值的第二意见。
SummarySince 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.
总结由于您正在运行 SQL 2008，请考虑启用对即席查询计划的优化。如果您的工作负载主要是临时的，这将对内存压力有很大帮助。
回答by Christoffer Lette
Here's a guess:
这是一个猜测：
One thing that strikes me is that you have a Working Setof 15 GBwhile the Available Physical Memoryis 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.)
令我惊讶的一件事是，你有一个Working Set的15 GB，而Available Physical Memory仅仅是258 MB。我相信您应该为 Sql Server 提供更多内存。（这是否只是将滑块向右移动一点，和/或安装更多 RAM，我不知道。）
回答by Michael K. Campbell
The docs for DBCC MEMORYSTATUS() are here: http://support.microsoft.com/kb/907877
DBCC MEMORYSTATUS() 的文档在这里：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.
它们不是非常冗长 - 但至少会让您了解您正在查看的内容。
