sql server 2008 701错误

Question

操作系统 win server 2003 64位
数据库   sql server 2008 64位
物理内存  32G
max server memory 20G
现状：达到max server memory 后会报701错误：
      Failed allocate pages: FAIL_PAGE_ALLOCATION 1
     错误: 701，严重性: 17，状态: 65。
    There is insufficient system memory in resource pool 'internal' to run this query.
DBCC MEMORYSTATUS输出如下：其中MEMORYCLERK_SQLOPTIMIZER
占用不正常。怎样定位什么原因导致MEMORYCLERK_SQLOPTIMIZER分配大量内存，为什么会报701错误？？Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              33706028
VM Committed                             15794080
Locked Pages Allocated                   0
Reserved Memory                          1024
Reserved Memory In Use                   0(5 行受影响)Memory node Id = 0                       KB
---------------------------------------- -----------
VM Reserved                              12032
VM Committed                             11648
Locked Pages Allocated                   0
MultiPage Allocator                      9640
SinglePage Allocator                     12495064(5 行受影响)Memory node Id = 1                       KB
---------------------------------------- -----------
VM Reserved                              33690156
VM Committed                             15778740
Locked Pages Allocated                   0
MultiPage Allocator                      70312
SinglePage Allocator                     12495064(5 行受影响)Memory node Id = 64                      KB
---------------------------------------- -----------
VM Reserved                              2752
VM Committed                             2656
Locked Pages Allocated                   0
MultiPage Allocator                      2576
SinglePage Allocator                     12495064(5 行受影响)MEMORYCLERK_SQLGENERAL (node 0)          KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     4240
MultiPage Allocator                      26400(7 行受影响)MEMORYCLERK_SQLGENERAL (node 1)          KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     296
MultiPage Allocator                      0(7 行受影响)MEMORYCLERK_SQLGENERAL (Total)           KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     4536
MultiPage Allocator                      26400(7 行受影响)MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
---------------------------------------- -----------
VM Reserved                              33587200
VM Committed                             15691008
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     0
MultiPage Allocator                      416(7 行受影响)MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     224
MultiPage Allocator                      0(7 行受影响)MEMORYCLERK_SQLQUERYEXEC (node 1)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     696
MultiPage Allocator                      0(7 行受影响)MEMORYCLERK_SQLQUERYEXEC (Total)         KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     920
MultiPage Allocator                      0(7 行受影响)

MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     11935728
MultiPage Allocator                      944
(7 行受影响)Buffer Pool                              Value
---------------------------------------- -----------
Committed                                1911344
Target                                   2560000
Database                                 213326
Dirty                                    679
In IO                                    0
Latched                                  8
Free                                     136257
Stolen                                   1561761
Reserved                                 0
Visible                                  2560000
Stolen Potential                         870239
Limiting Factor                          17
Last OOM Factor                          0
Page Life Expectancy                     342407(14 行受影响)Process/System Counts                    Value
---------------------------------------- --------------------
Available Physical Memory                13777543168
Available Virtual Memory                 8760752377856
Available Paging File                    14831734784
Working Set                              16328929280
Percent of Committed Memory in WS        100
Page Faults                              466370290
System physical memory high              1
System physical memory low               0
Process physical memory low              0
Process virtual memory low               0(10 行受影响)Procedure Cache                          Value
---------------------------------------- -----------
TotalProcs                               3116
TotalPages                               48199
InUsePages                               372(3 行受影响)Query Memory Objects (internal)          Value
---------------------------------------- -----------
Grants                                   0
Waiting                                  0
Available                                773391
Current Max                              773391
Future Max                               773391
Physical Max                             1883046
Next Request                             0
Waiting For                              0
Cost                                     0
Timeout                                  0
Wait Time                                0(11 行受影响)Small Query Memory Objects (internal)    Value
---------------------------------------- -----------
Grants                                   0
Waiting                                  0
Available                                40704
Current Max                              40704
Future Max                               40704(5 行受影响)Optimization Queue (internal)            Value
---------------------------------------- --------------------
Overall Memory                           16809984000
Target Memory                            15607865344
Last Notification                        1
Timeout                                  6
Early Termination Factor                 5(5 行受影响)Memory Pool (internal)                   Pages
---------------------------------------- -----------
Allocations                              1561974
Predicted                                2034390
Private Target                           0
Private Limit                            0
Total Target                             2432000
Total Limit                              2432000
OOM Count                                0(7 行受影响)MEMORYBROKER_FOR_CACHE (internal)        Pages
---------------------------------------- -----------
Allocations                              54327
Rate                                     1646
Target Allocations                       453583
Future Allocations                       0
Overall                                  2052000
Last Notification                        1(6 行受影响)MEMORYBROKER_FOR_STEAL (internal)        Pages
---------------------------------------- -----------
Allocations                              1507647
Rate                                     -172
Target Allocations                       1905257
Future Allocations                       0
Overall                                  2052000
Last Notification                        1(6 行受影响)MEMORYBROKER_FOR_RESERVE (internal)      Pages
---------------------------------------- -----------
Allocations                              0
Rate                                     -1879
Target Allocations                       868380
Future Allocations                       470761
Overall                                  2052000
Last Notification                        1(6 行受影响)
sql server数据库内存optimizationoom

Answer 1

--参考下这2篇文章吧。楼主可否计算下，运行那句SQL的数据量大概有多大？如果远远小于20G，考虑其它原因。
http://social.msdn.microsoft.com/Forums/en-US/8549320f-3280-4aeb-8ed9-d1e6174be739/-default-
http://technet.microsoft.com/zh-cn/library/cc627395.aspx

Answer 2

建议将max server memory设为2147483647,让SQL引擎自动分配使用内存即可,
只要系统稳定运行,占用内存多并不是什么问题,不应去限制最大使用内存.-- 查看内存使用情况
select 
type,
sum(virtual_memory_reserved_kb) as [VM Reserved],
sum(virtual_memory_committed_kb) as [VM Committed],
sum(awe_allocated_kb) as [AWE Allocated],
sum(shared_memory_reserved_kb) as [SM Reserved], 
sum(shared_memory_committed_kb) as [SM Committed],
sum(multi_pages_kb) as [MultiPage Allocator],
sum(single_pages_kb) as [SinlgePage Allocator]
FROM sys.dm_os_memory_clerks
where type like '%SQLCP'
group by type
order by type

Answer 3

很大一部分在SQLOPTIMIZER 的部分，可能是由于过多的Ad-hoc query造成的或者是由于特别复杂的语句导致（比如IN里面的数量太多导致），可以在这方面下手查。

Answer 4

这个需要做一段时间的监测，以前也遇到过类似的现象，但问题并没有再重现：
http://www.cnblogs.com/nzperfect/archive/2012/04/24/2467689.html

Answer 5

问一下optimizer 是在哪个过程占用的，具体应该怎么跟踪到是哪个sql占用的么？

Answer 6

问一下optimizer 是在哪个过程占用的，具体应该怎么跟踪到是哪个sql占用的么？
老实说这块我也不是很清楚，不知道SQL Server会不会在哪里有记录。根据MSDN上面的说法是：
Troubleshooting Regardless of whether the error is consistent and repeatable (same state) or random (appears at random times with different states), you will need to investigate server memory distribution during the time you see this error
看到也是要在发生的时候调查，没有提到可以在发生之后的一段时间如何查找问题原因。　

Answer 7

通过什么办法可以定位哪些adhoc 占用optimizer呢？

Answer 8

问一下optimizer 是在哪个过程占用的，具体应该怎么跟踪到是哪个sql占用的么？
老实说这块我也不是很清楚，不知道SQL Server会不会在哪里有记录。根据MSDN上面的说法是：
Troubleshooting Regardless of whether the error is consistent and repeatable (same state) or random (appears at random times with different states), you will need to investigate server memory distribution during the time you see this error
看到也是要在发生的时候调查，没有提到可以在发生之后的一段时间如何查找问题原因。　
sql server 2008 R2,没打过补丁，会不会跟这个有关？？

Answer 9

SinglePage Allocator                     11935728
MultiPage Allocator                      944不管是SQL SERVER，还是ORACLE，都会对内部各组件（非Data Buffer）分配一定比例的资源（RAM），超过报错也不奇怪，只是机率很小而已
解决办法：
治标，定期重启动，或升级到Win Svr 2008，其内存管理和分配方式有改进
治本，从设计、开发上着手改进