查询计划未保留内存不足错误

Question

我们一直在经历Server的内存问题。

当我们开始获得超时和登录错误时，我们第一次意识到我们遇到了问题：

成功地与服务器建立了连接，但在登录过程中发生了错误。(提供者: TCP提供程序，错误:0-指定的网络名称不再可用。)

查看sqlbox上的事件查看器，我们注意到大量内存不足的错误：

系统内存不足，无法运行此查询。有关更多信息，请参见http://go.microsoft.com/fwlink/events.asp的帮助和支持中心。

在此之前唯一的即时警告是以下信息：

AppDomain 119 (Alerts.dbo运行时.118)卸载。

在此之前大约20分钟，我们收到了一些与perf相关的消息和错误：

信息:此计算机上的从其MOM服务器接收新规则和配置设置。管理组: GGC警告: 用于"ASP“服务的性能库"C:\WINDOWS\system32\aspperf.dll”的配置信息与存储在注册表中的可信性能库信息不匹配。此库中的函数将不视为受信任的函数。错误:无法访问计算机上的性能计数器。Microsoft操作管理器将不会监视此计算机上的性能计数器，直到它们可用为止。信息: ，微软操作经理成功地在计算机上加载了性能计数器，在之前的失败(S)之后，并将开始监视它们。

我怀疑上述perf警报/错误是否与“内存异常不足”的两个小时有关，但我已经包含了这些消息，以防万一。

最后，经过两个小时的红色内存错误，以下信息消息预示着内存不足警报的结束：

由于“DBCC FREEPROCCACHE”或“DBCC FREESYSTEMCACHE”操作，Server遇到了“绑定树”cachestore (计划缓存的一部分)的2次出现(S)。

所以我们的DBA在某个时候调用了freeprocache。尽管最终修复了内存不足的异常，但我们注意到我们的执行计划仍然没有被存储。这个问题已经持续了3天，这意味着使用复杂计划查询的应用程序正面临严重的性能困难。有一些地方，计划开始采取再次，但他们永远不会停留在缓存很长时间。

我想知道有没有人能帮我找出关注的领域。

A部分表示保存查询计划时的系统(计划被保留，但只保留一个小时左右)，B部分表示计划根本不被缓存时(检查dm_exec_query_stats)

A部分

DBCC MemoryStatus结果：

Memory Manager   KB 
VM Reserved 1828768
VM Committed    269928
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory node Id = 0   KB 
VM Reserved 1824608
VM Committed    265920
AWE Allocated   13762560
MultiPage Allocator 50776
SinglePage Allocator    656568

 MEMORYCLERK_SQLGENERAL (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   14672
 MultiPage Allocator    11144

MEMORYCLERK_SQLBUFFERPOOL (Total)    KB 
 VM Reserved    1620024
 VM Committed   137272
 AWE Allocated  13762560
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    3624

MEMORYCLERK_SQLQUERYEXEC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   80
 MultiPage Allocator    32

MEMORYCLERK_SQLOPTIMIZER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   5568
 MultiPage Allocator    88

MEMORYCLERK_SQLUTILITIES (Total)     KB 
 VM Reserved    360
 VM Committed   360
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   664
 MultiPage Allocator    0

MEMORYCLERK_SQLSTORENG (Total)   KB 
 VM Reserved    17792
 VM Committed   17792
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   11200
 MultiPage Allocator    13040

MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   4504
 MultiPage Allocator    0

MEMORYCLERK_SQLCLR (Total)   KB 
 VM Reserved    126336
 VM Committed   54816
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1296
 MultiPage Allocator    2872

MEMORYCLERK_SQLSERVICEBROKER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   176
 MultiPage Allocator    192

MEMORYCLERK_SQLHTTP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

MEMORYCLERK_SNI (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   432
 MultiPage Allocator    16

MEMORYCLERK_FULLTEXT (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_SQLXP (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_BHF (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   720
 MultiPage Allocator    0

MEMORYCLERK_SQLQERESERVATIONS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   37896
 MultiPage Allocator    0

MEMORYCLERK_HOST (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   224
 MultiPage Allocator    96

MEMORYCLERK_SOSNODE (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16008
 MultiPage Allocator    9136

CACHESTORE_OBJCP (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   297080
 MultiPage Allocator    4448

CACHESTORE_PHDR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16400
 MultiPage Allocator    0

CACHESTORE_XPROC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   112
 MultiPage Allocator    0

CACHESTORE_TEMPTABLES (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   48
 MultiPage Allocator    0

CACHESTORE_NOTIF (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_VIEWDEFINITIONS (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_XMLDBTYPE (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBELEMENT (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBATTRIBUTE (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_STACKFRAMES (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    8

CACHESTORE_BROKERTBLACS (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   296
 MultiPage Allocator    0

CACHESTORE_BROKERKEK (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERDSH (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERUSERCERTLOOKUP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERRSB (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERREADONLY (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   32
 MultiPage Allocator    0

CACHESTORE_BROKERTO (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_EVENTS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_SYSTEMROWSET (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   3104
 MultiPage Allocator    0

USERSTORE_SCHEMAMGR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   9592
 MultiPage Allocator    144

USERSTORE_DBMETADATA (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   6800
 MultiPage Allocator    0

USERSTORE_TOKENPERM (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   752
 MultiPage Allocator    0

USERSTORE_OBJPERM (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   6072
 MultiPage Allocator    0

USERSTORE_SXC (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2232
 MultiPage Allocator    0

OBJECTSTORE_LBSS (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   472
 MultiPage Allocator    0

OBJECTSTORE_SNI_PACKET (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   7640
 MultiPage Allocator    48

OBJECTSTORE_SERVICE_BROKER (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   256
 MultiPage Allocator    0

OBJECTSTORE_LOCK_MANAGER (Total)     KB 
 VM Reserved    4096
 VM Committed   4096
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2584
 MultiPage Allocator    0

Buffer Distribution Buffers
Stolen  11432
Free    283
Cached  75066
Database (clean)    1609444
Database (dirty)    24091
I/O 0
Latched 4

Buffer Counts   Buffers
Committed   1720320
Target  1720320
Hashed  1633540
Stolen Potential    89382
External Reservation    308
Min Free    712
Visible 185344
Available Paging File   2268071

Procedure Cache Value
TotalProcs  6240
TotalPages  71392
InUsePages  407

Global Memory Objects   Buffers
Resource    1127
Locks   326
XDES    204
SETLS   8
SE Dataset Allocators   16
SubpDesc Allocators 8
SE SchemaManager    1194
SQLCache    749
Replication 126
ServerGlobal    27
XP Global   2
SortTables  1523

Query Memory Objects    Value
Grants  2
Waiting 0
Available (Buffers) 78932
Maximum (Buffers)   83833
Limit   83833
Next Request    0
Waiting For 0
Cost    0
Timeout 0
Wait Time   0
Last Target 88245

Small Query Memory Objects  Value
Grants  0
Waiting 0
Available (Buffers) 4410
Maximum (Buffers)   4410
Limit   4410

Optimization Queue  Value
Overall Memory  1216954368
Target Memory   556490752
Last Notification   1
Timeout 6
Early Termination Factor    5

Small Gateway   Value
Configured Units    32
Available Units 32
Acquires    0
Waiters 0
Threshold Factor    250000
Threshold   250000

Medium Gateway  Value
Configured Units    8
Available Units 8
Acquires    0
Waiters 0
Threshold Factor    12

Big Gateway Value
Configured Units    1
Available Units 1
Acquires    0
Waiters 0
Threshold Factor    8

MEMORYBROKER_FOR_CACHE  Value
Allocations 75056
Rate    15
Target Allocations  136125
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_STEAL  Value
Allocations 6869
Rate    8
Target Allocations  67931
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_RESERVE    Value
Allocations 4737
Rate    1844
Target Allocations  94128
Future Allocations  33074
Last Notification   1

可用内存和最大的空闲连续块：

Total avail Mem, KB   Max free size, KB
17828                  4148

B部分：

DBCC MemoryStatus

Memory Manager   KB 
VM Reserved 1823056
VM Committed    264192
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory node Id = 0   KB 
VM Reserved 1818896
VM Committed    260184
AWE Allocated   13762560
MultiPage Allocator 41672
SinglePage Allocator    90880

MEMORYCLERK_SQLGENERAL (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   9456
 MultiPage Allocator    12416

MEMORYCLERK_SQLBUFFERPOOL (Total)    KB 
 VM Reserved    1620024
 VM Committed   137272
 AWE Allocated  13762560
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    3624

MEMORYCLERK_SQLQUERYEXEC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   88
 MultiPage Allocator    0

MEMORYCLERK_SQLOPTIMIZER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   11344
 MultiPage Allocator    88

MEMORYCLERK_SQLUTILITIES (Total)     KB 
 VM Reserved    600
 VM Committed   600
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   672
 MultiPage Allocator    0

MEMORYCLERK_SQLSTORENG (Total)   KB 
 VM Reserved    17856
 VM Committed   17856
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   11408
 MultiPage Allocator    13040

MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2648
 MultiPage Allocator    0

MEMORYCLERK_SQLCLR (Total)   KB 
 VM Reserved    126336
 VM Committed   54816
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1296
 MultiPage Allocator    2872

MEMORYCLERK_SQLSERVICEBROKER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   184
 MultiPage Allocator    192

MEMORYCLERK_SQLHTTP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

MEMORYCLERK_SNI (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   488
 MultiPage Allocator    16

MEMORYCLERK_FULLTEXT (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_SQLXP (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_QSRANGEPREFETCH (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   392
 MultiPage Allocator    0

MEMORYCLERK_BHF (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   456
 MultiPage Allocator    0

MEMORYCLERK_SQLQERESERVATIONS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   21512
 MultiPage Allocator    0

MEMORYCLERK_HOST (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   224
 MultiPage Allocator    96

MEMORYCLERK_SOSNODE (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   14904
 MultiPage Allocator    8992

CACHESTORE_OBJCP (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   3816
 MultiPage Allocator    0

CACHESTORE_SQLCP (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   328
 MultiPage Allocator    0

CACHESTORE_PHDR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1760
 MultiPage Allocator    0

CACHESTORE_XPROC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_TEMPTABLES (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_NOTIF (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_VIEWDEFINITIONS (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_XMLDBTYPE (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBELEMENT (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBATTRIBUTE (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_STACKFRAMES (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    8

CACHESTORE_BROKERTBLACS (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   304
 MultiPage Allocator    0

CACHESTORE_BROKERKEK (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERDSH (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERUSERCERTLOOKUP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERRSB (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERREADONLY (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   32
 MultiPage Allocator    0

CACHESTORE_BROKERTO (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_EVENTS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_SYSTEMROWSET (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   744
 MultiPage Allocator    0

USERSTORE_SCHEMAMGR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   9560
 MultiPage Allocator    144

USERSTORE_DBMETADATA (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2960
 MultiPage Allocator    0

USERSTORE_TOKENPERM (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1064
 MultiPage Allocator    0

USERSTORE_OBJPERM (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   6144
 MultiPage Allocator    0

USERSTORE_SXC (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   88
 MultiPage Allocator    0

OBJECTSTORE_LBSS (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   160
 MultiPage Allocator    0

OBJECTSTORE_SNI_PACKET (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   7464
 MultiPage Allocator    48

OBJECTSTORE_SERVICE_BROKER (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   256
 MultiPage Allocator    0

OBJECTSTORE_LOCK_MANAGER (Total)     KB 
 VM Reserved    4096
 VM Committed   4096
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1680
 MultiPage Allocator    0

Buffer Distribution Buffers
Stolen  8130
Free    3291
Cached  4433
Database (clean)    1685517
Database (dirty)    18929
I/O 4
Latched 16

Buffer Counts   Buffers
Committed   1720320
Target  1720320
Hashed  1704528
Stolen Potential    162293
External Reservation    1538
Min Free    1024
Visible 185344
Available Paging File   2256907

Procedure Cache Value
TotalProcs  11
TotalPages  813
InUsePages  137

Global Memory Objects   Buffers
Resource    1141
Locks   213
XDES    225
SETLS   8
SE Dataset Allocators   16
SubpDesc Allocators 8
SE SchemaManager    1190
SQLCache    80
Replication 126
ServerGlobal    27
XP Global   2
SortTables  1523

Query Memory Objects    Value
Grants  1
Waiting 0
Available (Buffers) 129608
Maximum (Buffers)   132297
Limit   132297
Next Request    0
Waiting For 0
Cost    0
Timeout 0
Wait Time   0
Last Target 139260

Small Query Memory Objects  Value
Grants  0
Waiting 0
Available (Buffers) 6963
Maximum (Buffers)   6963
Limit   6963

Optimization Queue  Value
Overall Memory  1216954368
Target Memory   1142743040
Last Notification   1
Timeout 6
Early Termination Factor    5

Small Gateway   Value
Configured Units    32
Available Units 28
Acquires    4
Waiters 0
Threshold Factor    250000
Threshold   250000

Medium Gateway  Value
Configured Units    8
Available Units 8
Acquires    0
Waiters 0
Threshold Factor    12
Threshold   23807146

Big Gateway Value
Configured Units    1
Available Units 1
Acquires    0
Waiters 0
Threshold Factor    8

MEMORYBROKER_FOR_CACHE  Value
Allocations 4347
Rate    -840
Target Allocations  136775
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_STEAL  Value
Allocations 6929
Rate    -702
Target Allocations  139495
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_RESERVE    Value
Allocations 2689
Rate    -60
Target Allocations  148554
Future Allocations  33074
Last Notification   1

留下的记忆：

Total avail Mem, KB Max free size, KB
18228          2580

A部分和B部分都是在内存不足的地方拍摄的。不同的是，对于B，查询计划似乎在任何一段时间内都不会被保留(在A中，它们被保存一个小时左右)。

我希望有人能够查看内存状态，并可能指出问题所在的方向。

另外，我们使用的是Server 2005 Server包3。

更新

好的，我看了上面的MemoryStatus，注意到第A部分的对象缓存存储是297 32，在32位上运行这么高吗？这难道不消耗大部分的VAS吗？

我想更详细地看一下这个：

因此，我一直在运行以下查询：

--Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS [Plan Cache Size(GB)]
FROM sys.dm_os_memory_cache_counters 
WHERE 
type = 'CACHESTORE_SQLCP'
OR 
type = 'CACHESTORE_OBJCP'

这似乎是骑自行车每两分钟左右，随着商店被冲，每提姆它的统计上升到200 to以上。其中大部分(如180 is )都在CACHESTORE_OBJCP中。

我是否正确地认为我可以使用下面的查询来分析对象缓存？

select  TOP 8000
 objtype,
    usecounts, 
 p.size_in_bytes/1024 'IN KB',
 LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY 'IN KB' DESC

以上查询大约位于缓存MB中的最高点，返回大约1500个对象。或多或少，前25位的大小似乎是触发器，之和大小为65 to。这是正常的吗？我撞错树了吗？

Answer 1

我猜你上传的“记忆左”输出来自克里斯蒂安·博尔顿的VAS使用分析脚本。对吗？如果是这样的话，您在memtoleave区域中只有17-18MB可用，这可能会在某个时候造成问题，并可能导致粘贴的全部或大部分错误消息。

乔纳森·凯哈伊亚斯( Jonathan 这里 )的这个查询将让您了解在memtoleave区域使用内存的方式：

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

这篇文章还很好地解释了memtoleave。

您询问的缓存的大小是否是一个问题取决于许多事情。由于这是一个32位的实例，需要考虑的更相关的事情之一是，对这些缓存的分配是使用使用缓冲池的单页分配器分配的，还是使用多页分配器分配的，后者在Server 2012之前并不使用缓冲池，而是使用memtoleave区域中的内存。您有大约13 GB分配给缓冲池，因此分配几百MB用于此或这不一定会造成问题。但是，memtoleave区域中的几百MB就足以导致您列出的一些错误。

Kehayias的文章解释了这些概念以及解决问题的选择，如果确实是memtoleave耗尽。

我将主要关注memtoleave领域的需求，以及Windows中的总体内存可用性，以及Server中内存可用性的一般度量，如页面预期寿命。

我还会考虑这个问题是否有助于为迁移到64位版本的Server提供业务支持，因为这样可以避免memtoleave问题，除非整个服务器内存不足。在64位构建上，理解和管理Server的内存使用要简单得多，特别是在Server 2012上，其中所有分配都通过缓冲池。您还可以考虑应用SP4，因为SP3中有许多已知的bug。

Answer 2

您正在运行32位版本的Server。无论您做什么，执行计划缓存所能拥有的最大内存是1G。升级到64位的机器对于解决您的问题至关重要。