SQLServer 内存溢出问题？（待解决）
发布时间：2020-12-12 12:51:36 所属栏目：MsSql教程 来源：网络整理
导读：报错是在3个小时前的，但是问题还没有立即出现，3个小时左右，Windows系统操作总是出错，数据库连接不上。SQLserver代理总是自动停止，启动几分钟就停止。 部分错误如下： ? --查看日志是否报内存信息exec xp_readerrorlog 1,1,'Memory',NULL,'2015-01-23 13
|
报错是在3个小时前的，但是问题还没有立即出现，3个小时左右，Windows系统操作总是出错，数据库连接不上。SQLserver代理总是自动停止，启动几分钟就停止。 部分错误如下：
--查看日志是否报内存信息 exec xp_readerrorlog 1,1,'Memory',NULL,'2015-01-23 13:00:00','2015-01-23 16:20:00','ASC' exec xp_readerrorlog 1,'2015-01-23 13:34:00','2015-01-23 13:34:30','ASC'
看到有内存输出情况！ Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536 Memory Manager KB ---------------------------------------- ---------- VM Reserved 76360868 VM Committed 64701408 Locked Pages Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 Memory node Id = 0 KB ---------------------------------------- ---------- VM Reserved 76357220 VM Committed 64697872 Locked Pages Allocated 0 MultiPage Allocator 1621392 SinglePage Allocator 1686352 Memory node Id = 64 KB ---------------------------------------- ---------- VM Reserved 2560 VM Committed 2504 Locked Pages Allocated 0 MultiPage Allocator 2416 SinglePage Allocator 1686352 MEMORYCLERK_SQLGENERAL (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16184 MultiPage Allocator 6928 MEMORYCLERK_SQLBUFFERPOOL (node 0) KB ---------------------------------------- ---------- VM Reserved 67158016 VM Committed 62676608 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 912 MEMORYCLERK_SQLQUERYEXEC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 416 MultiPage Allocator 0 MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 768 MultiPage Allocator 896 MEMORYCLERK_SQLUTILITIES (node 0) KB ---------------------------------------- ---------- VM Reserved 840 VM Committed 840 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 136 MultiPage Allocator 0 MEMORYCLERK_SQLSTORENG (node 0) KB ---------------------------------------- ---------- VM Reserved 14016 VM Committed 14016 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24520 MultiPage Allocator 5176 MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32424 MultiPage Allocator 0 MEMORYCLERK_SQLCLR (node 0) KB ---------------------------------------- ---------- VM Reserved 6314812 VM Committed 108128 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1488 MultiPage Allocator 10448 MEMORYCLERK_SQLSERVICEBROKER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 152 MultiPage Allocator 544 MEMORYCLERK_SQLHTTP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 MEMORYCLERK_SNI (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3984 MultiPage Allocator 16 MEMORYCLERK_SNI (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 16 MEMORYCLERK_SNI (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3984 MultiPage Allocator 32 MEMORYCLERK_FULLTEXT (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 MEMORYCLERK_SQLXP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 MEMORYCLERK_BHF (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1200 MultiPage Allocator 0 MEMORYCLERK_SQLQERESERVATIONS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 960 MultiPage Allocator 0 MEMORYCLERK_XE_BUFFER (node 0) KB ---------------------------------------- ---------- VM Reserved 4224 VM Committed 4224 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 0 MEMORYCLERK_HOST (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 MEMORYCLERK_SOSNODE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 106432 MultiPage Allocator 13832 MEMORYCLERK_SOSNODE (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 2336 MEMORYCLERK_SOSNODE (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 106440 MultiPage Allocator 16168 MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 96 SM Committed 96 SinglePage Allocator 0 MultiPage Allocator 0 MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 MEMORYCLERK_XE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 4024 MultiPage Allocator 320 CACHESTORE_OBJCP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 412192 MultiPage Allocator 15600 CACHESTORE_SQLCP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 542632 MultiPage Allocator 36624 CACHESTORE_PHDR (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 19912 MultiPage Allocator 0 CACHESTORE_XPROC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 112 MultiPage Allocator 0 CACHESTORE_TEMPTABLES (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 168 MultiPage Allocator 0 CACHESTORE_NOTIF (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_VIEWDEFINITIONS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_XMLDBTYPE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 CACHESTORE_XMLDBELEMENT (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_XMLDBATTRIBUTE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_STACKFRAMES (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 CACHESTORE_STACKFRAMES (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 CACHESTORE_STACKFRAMES (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 16 CACHESTORE_BROKERTBLACS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 56 MultiPage Allocator 0 CACHESTORE_BROKERKEK (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERDSH (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERRSB (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_BROKERREADONLY (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 CACHESTORE_BROKERTO (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 CACHESTORE_EVENTS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 CACHESTORE_CLRPROC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 CACHESTORE_SYSTEMROWSET (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3944 MultiPage Allocator 0 CACHESTORE_CONVPRI (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 64 MultiPage Allocator 0 CACHESTORE_FULLTEXTSTOPLIST (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 USERSTORE_SCHEMAMGR (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 73512 MultiPage Allocator 0 USERSTORE_DBMETADATA (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 13352 MultiPage Allocator 0 USERSTORE_TOKENPERM (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 226488 MultiPage Allocator 1522896 USERSTORE_OBJPERM (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3392 MultiPage Allocator 32 USERSTORE_SXC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 15864 MultiPage Allocator 0 USERSTORE_SXC (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 USERSTORE_SXC (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 15872 MultiPage Allocator 0 OBJECTSTORE_LBSS (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 240 MultiPage Allocator 6288 OBJECTSTORE_SNI_PACKET (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 54240 MultiPage Allocator 56 OBJECTSTORE_SNI_PACKET (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 56 OBJECTSTORE_SNI_PACKET (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 54240 MultiPage Allocator 112 OBJECTSTORE_SERVICE_BROKER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 424 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (node 0) KB ---------------------------------------- ---------- VM Reserved 262144 VM Committed 262144 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 127616 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (node 64) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------- ---------- VM Reserved 262144 VM Committed 262144 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 127640 MultiPage Allocator 0 OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 Buffer Pool Value ---------------------------------------- ---------- Committed 7584275 Target 7584275 Database 7373009 Dirty 15408 In IO 0 Latched 1 Free 456 Stolen 210810 Reserved 160 Visible 7584275 Stolen Potential 6994699 Limiting Factor 18 Last OOM Factor 0 Page Life Expectancy 7012 Process/System Counts Value ---------------------------------------- ---------- Available Physical Memory 439861248 Available Virtual Memory 8717305466880 Available Paging File 5242880 Working Set 64049631232 Percent of Committed Memory in WS 96 Page Faults 50018252 System physical memory high 1 System physical memory low 0 Process physical memory low 0 Process virtual memory low 0 Procedure Cache Value ---------------------------------------- ---------- TotalProcs 10926 TotalPages 128391 InUsePages 663 Global Memory Objects Pages ---------------------------------------- ---------- Resource 1086 Locks 15955 XDES 2271 SETLS 4 SE Dataset Allocators 8 SubpDesc Allocators 4 SE SchemaManager 2209 SE Column Metadata Cache 6979 SQLCache 1896 Replication 1395 ServerGlobal 51 XP Global 2 SortTables 35 Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 5211490 Current Max 5211490 Future Max 5211490 Physical Max 5686510 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 12800 Current Max 12800 Future Max 12800 Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 4 Waiting 0 Available 5686350 Current Max 5686510 Future Max 5686510 Physical Max 5686510 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 12800 Current Max 12800 Future Max 12800 Optimization Queue (internal) Value ---------------------------------------- ---------- Overall Memory 49801379840 Target Memory 45764108288 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (internal) Value ---------------------------------------- ---------- Configured Units 16 Available Units 16 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (internal) Value ---------------------------------------- ---------- Configured Units 4 Available Units 4 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 Optimization Queue (default) Value ---------------------------------------- ---------- Overall Memory 49801379840 Target Memory 45734273024 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (default) Value ---------------------------------------- ---------- Configured Units 16 Available Units 16 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (default) Value ---------------------------------------- ---------- Configured Units 4 Available Units 4 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (default) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Memory Pool Manager Pages ---------------------------------------- ---------- Reserved Current 160 Reserved Limit 6994264 Memory Pool (internal) Pages ---------------------------------------- ---------- Allocations 53736 Predicted 53736 Private Target 0 Private Limit 0 Total Target 7205061 Total Limit 7205061 OOM Count 0 MEMORYBROKER_FOR_CACHE (internal) Pages ---------------------------------------- ---------- Allocations 39888 Rate 0 Target Allocations 5612479 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYBROKER_FOR_STEAL (internal) Pages ---------------------------------------- ---------- Allocations 13848 Rate -23 Target Allocations 5586439 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYBROKER_FOR_RESERVE (internal) Pages ---------------------------------------- ---------- Allocations 0 Rate 0 Target Allocations 5572591 Future Allocations 0 Overall 6079270 Last Notification 1 Memory Pool (default) Pages ---------------------------------------- ---------- Allocations 157181 Predicted 1578734 Private Target 0 Private Limit 0 Total Target 7205061 Total Limit 7205061 OOM Count 0 MEMORYBROKER_FOR_CACHE (default) Pages ---------------------------------------- ---------- Allocations 146897 Rate 4 Target Allocations 5719492 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYBROKER_FOR_STEAL (default) Pages ---------------------------------------- ---------- Allocations 10164 Rate 42 Target Allocations 5582797 Future Allocations 0 Overall 6079270 Last Notification 1 MEMORYBROKER_FOR_RESERVE (default) Pages ---------------------------------------- ---------- Allocations 120 Rate -492 Target Allocations 6079270 Future Allocations 1421627 Overall 6079270 Last Notification 1 比较多，其实就相当于以下的两个语句的查询结果： SELECT type,SUM(virtual_memory_reserved_kb) AS [vm reserved],SUM(virtual_memory_committed_kb) AS [vm commited],SUM(awe_allocated_kb) AS [awe allocated],SUM(shared_memory_reserved_kb) AS [sm reserved],SUM(shared_memory_committed_kb) AS [sm committed],SUM(single_pages_kb) AS [Stolen in Buffer Pool],SUM(multi_pages_kb) AS [MemToLeave],SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen],SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool] FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY type dbcc memorystatus 先看输出头信息： Failed VirtualAllocateBytes:FAIL_VIRTUAL_RESERVE 65536 这被认为Memtoleave 不足的情况。 对以下两种情况，通常认为buffer pool内的stolen不足，即single_pages不足。 Failed?Virtual?Allocate?Bytes:?FAIL_VIRTUAL_RESERVE? Failed allocatepages:FAIL_PAGE_ALLOCATION ? 再看当时在数据库运行查询时的错误： 消息701,级别 17,状态 123,第 2 行资源池 'default'没有足够的系统内存来运行此查询 消息701，也表明?Stolen 内存有压力。 现在先了解什么是Stolen ，multi_pages，Memtoleave? Stolen ： 对于内存的请求访问，SQLserver大部分是先是reserved（预留）一块大的内存，再往内存中一小块一小块地commit（提交），这样可以很好地管理物理内存的访问，然而也有少部分数据，不先reserved，不管有没有地址空间，都直接提交，这就是所谓的 [偷] ，在SQLserver里叫 【stolen】 stolen 通常不会太大,SQL语句的执行过程都需要用到.除非游标未关闭或者内存溢出才增大.? multi_pages，Memtoleave?： SQLserver数据库通常以8KB的数据进行内存的请求访问，和数据页大小一致，可更有效地管理（大部分存在buffer pool中）。对于大于8KB的内存申请，SQLserver把它们集中在另外一个区域，成为【multi-pages】或者【Memtoleave?】，这种分配也成为 multi-pages allocation。 哪些内存申请会使用到 multi-pages： 各种内存分类方法之间的关系：
|
