none
求帮。。。。。HELP SQL2088耗尽内存,数据库崩溃 RRS feed

  • 问题

  • 求帮。。。。。HELP

    我的服务器内存为58G。windows server 2008 r2+SQL SERVER 2008 R2...SQL配置已设了最大使用内存为50G。。。。可是今早突然发现服务器内存把58G都耗尽完毕,该服务器除了做SQL数据库服务,没有其他用途,后台执行SQL语句查询缓慢,没反应。SQL日志一直报信息:

    消息
    MEMORYCLERK_SQLOPTIMIZER (Total)                 KB
    ---------------------------------------- ----------
    VM Reserved                                       0
    VM Committed                                      0
    Locked Pages Allocated                            0
    SM Reserved                                       0
    SM Committed                                      0
    SinglePage Allocator                          65840
    MultiPage Allocator                             960

    重新启动服务器后又正常回,不知道什么原因,是内存耗尽,还是其他什么原因呢?为什么SQL server 2008不会自己释放内存?。。。。。。。。求解

     

    2011年11月8日 2:46

答案

全部回复

  • What's page file size on the server? Sql will not use more memory than max memory setting (50gb in your case if you set properly).
    • 已建议为答案 Jacky_shen 2011年11月10日 15:58
    2011年11月8日 2:53
  • What's page file size on the server? Sql will not use more memory than max memory setting (50gb in your case if you set properly).


    rmiao,请问下,page file size和内存是否有什么特别的配置呢?

    比如我内存是多少,那么我的page file size 按照什么比列来设置呢?

    还有就是page file是不是只有在C盘才有的 


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    • 已编辑 Wison-Ho 2011年11月8日 3:29
    2011年11月8日 3:23
  • Page file should have same size as physical memory amount at least, can put on any local disk or multiple local disks on the server. 
    2011年11月8日 3:51
  • Page file should have same size as physical memory amount at least, can put on any local disk or multiple local disks on the server. 

    OK.了解 多谢
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年11月8日 5:05
  • What's page file size on the server? Sql will not use more memory than max memory setting (50gb in your case if you set properly).


    查询SQL日志。发现提示内存分配错误。。也就零辰5点13分开始一直报以下错误:

    11/08/2011 05:14:34,spid51,未知,错误: 701,严重性: 17,状态: 123。
    11/08/2011 05:14:34,spid51,未知,There is insufficient system memory in resource pool 'pMAX_CPU_PERCENT_10' to run this query.
    11/08/2011 05:14:34,spid51,未知,错误: 701,严重性: 17,状态: 123。
    11/08/2011 05:14:34,spid51,未知,There is insufficient system memory in resource pool 'pMAX_CPU_PERCENT_10' to run this query.
    11/08/2011 05:14:34,spid51,未知,错误: 701,严重性: 17,状态: 123。
    11/08/2011 05:13:22,spid52,未知,There is insufficient system memory in resource pool 'pMAX_CPU_PERCENT_10' to run this query.
    11/08/2011 05:13:22,spid52,未知,错误: 701,严重性: 17,状态: 123。
    11/08/2011 05:13:21,spid52,未知,There is insufficient system memory in resource pool 'pMAX_CPU_PERCENT_10' to run this query.
    11/08/2011 05:13:21,spid52,未知,错误: 701,严重性: 17,状态: 123。
    11/08/2011 05:13:21,spid52,未知,MEMORYBROKER_FOR_RESERVE (pMAX_CPU_PERCENT_10)      Pages
    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                    2876
    <nl/>Rate                                              0
    <nl/>Target Allocations                             2081
    <nl/>Future Allocations                                0
    <nl/>Overall                                       12287
    <nl/>Last Notification                                 2


    11/08/2011 05:13:21,spid52,未知,MEMORYBROKER_FOR_STEAL (pMAX_CPU_PERCENT_10)      Pages
    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                    9798
    <nl/>Rate                                           1204
    <nl/>Target Allocations                             7963
    <nl/>Future Allocations                                0
    <nl/>Overall                                       12287
    <nl/>Last Notification                                 2


    11/08/2011 05:13:21,spid52,未知,MEMORYBROKER_FOR_CACHE (pMAX_CPU_PERCENT_10)      Pages
    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                    3097
    <nl/>Rate                                           -171
    <nl/>Target Allocations                             2241
    <nl/>Future Allocations                                0
    <nl/>Overall                                       12287
    <nl/>Last Notification                                 2

    11/08/2011 05:13:21,spid52,未知,Memory Pool (pMAX_CPU_PERCENT_10)             Pages
    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                   15771
    <nl/>Predicted                                     16975
    <nl/>Private Target                                    0
    <nl/>Private Limit                                     0
    <nl/>Total Target                                  14563
    <nl/>Total Limit                                   15771
    <nl/>OOM Count                                         1


    11/08/2011 05:13:21,spid52,未知,MEMORYBROKER_FOR_RESERVE (default)            Pages
    <nl/>---------------------------------------- ----------<nl/>
    Allocations                                       0<nl/>Rate                                              0
    <nl/>Target Allocations                           117397
    <nl/>Future Allocations                                0
    <nl/>Overall                                      122880
    <nl/>Last Notification                                 1
    11/08/2011 05:13:21,spid52,未知,MEMORYBROKER_FOR_STEAL (default)              Pages


    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                    4910
    <nl/>Rate                                              0
    <nl/>Target Allocations                           122307
    <nl/>Future Allocations                                0
    <nl/>Overall                                      122880
    <nl/>Last Notification                                 1


    11/08/2011 05:13:21,spid52,未知,MEMORYBROKER_FOR_CACHE (default)              Pages
    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                    5917
    <nl/>Rate                                              0
    <nl/>Target Allocations                           122880
    <nl/>Future Allocations                                0
    <nl/>Overall                                      122880
    <nl/>Last Notification                                 1


    11/08/2011 05:13:21,spid52,未知,Memory Pool (default)                         Pages
    <nl/>---------------------------------------- ----------
    <nl/>Allocations                                   10827
    <nl/>Predicted                                     10827
    <nl/>Private Target                                    0
    <nl/>Private Limit                                     0
    <nl/>Total Target                                 145636
    <nl/>Total Limit                                  145636
    <nl/>OOM Count                                         0


    11/08/2011 05:13:21,spid52,未知,MEMORYCLERK_SQLOPTIMIZER (node 0)                KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                       0
    <nl/>VM Committed                                      0
    <nl/>Locked Pages Allocated                            0
    <nl/>SM Reserved                                       0
    <nl/>SM Committed                                      0
    <nl/>SinglePage Allocator                          63056
    <nl/>MultiPage Allocator                             944


    11/08/2011 05:13:21,spid52,未知,MEMORYCLERK_SQLQUERYEXEC (node 0)                KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                       0
    <nl/>VM Committed                                      0
    <nl/>Locked Pages Allocated                            0
    <nl/>SM Reserved                                       0
    <nl/>SM Committed                                      0
    <nl/>SinglePage Allocator                           4792
    <nl/>MultiPage Allocator                             520
    11/08/2011 05:13:21,spid52,未知,MEMORYCLERK_SQLBUFFERPOOL (node 0)               KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                58802176
    <nl/>VM Committed                                  65536
    <nl/>Locked Pages Allocated                      2049152
    <nl/>SM Reserved                                       0
    <nl/>SM Committed                                      0
    <nl/>SinglePage Allocator                              0
    <nl/>MultiPage Allocator                            1880
    11/08/2011 05:13:21,spid52,未知,MEMORYCLERK_SQLGENERAL (Total)                   KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                       0
    <nl/>VM Committed                                      0
    <nl/>Locked Pages Allocated                            0
    <nl/>SM Reserved                                       0
    <nl/>SM Committed                                      0
    <nl/>SinglePage Allocator                           4368
    <nl/>MultiPage Allocator                            7392

    11/08/2011 05:13:21,spid52,未知,MEMORYCLERK_SQLGENERAL (node 1)                  KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                       0
    <nl/>VM Committed                                      0
    <nl/>Locked Pages Allocated                            0
    <nl/>SM Reserved                                       0
    <nl/>SM Committed                                      0
    <nl/>SinglePage Allocator                            168
    <nl/>MultiPage Allocator                              16


    11/08/2011 05:13:21,spid52,未知,MEMORYCLERK_SQLGENERAL (node 0)                  KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                       0
    <nl/>VM Committed                                      0
    <nl/>Locked Pages Allocated                            0
    <nl/>SM Reserved                                       0
    <nl/>SM Committed                                      0
    <nl/>SinglePage Allocator                           4200
    <nl/>MultiPage Allocator                            7376
    11/08/2011 05:13:21,spid52,未知,Memory node Id = 64                              KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                    2752
    <nl/>VM Committed                                   2656
    <nl/>Locked Pages Allocated                            0
    <nl/>MultiPage Allocator                            2576
    <nl/>SinglePage Allocator                         202904

    11/08/2011 05:13:21,spid52,未知,Memory node Id = 1                               KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                58988704
    <nl/>VM Committed                                 238472
    <nl/>Locked Pages Allocated                      2085504
    <nl/>MultiPage Allocator                           31816
    <nl/>SinglePage Allocator                         202904


    11/08/2011 05:13:21,spid52,未知,Memory node Id = 0                               KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                   76352
    <nl/>VM Committed                                  75928
    <nl/>Locked Pages Allocated                            0
    <nl/>MultiPage Allocator                            8768
    <nl/>SinglePage Allocator                         202904


    11/08/2011 05:13:21,spid52,未知,Memory Manager                                   KB
    <nl/>---------------------------------------- ----------
    <nl/>VM Reserved                                59068896
    <nl/>VM Committed                                 318092
    <nl/>Locked Pages Allocated                      2085504
    <nl/>Reserved Memory                                1024
    <nl/>Reserved Memory In Use                            0

    11/08/2011 05:13:21,spid52,未知,Failed allocate pages: FAIL_PAGE_ALLOCATION 1

     

    不知道原因,会提示内存不够分配,按照说我之前的设置SQL2008最大使用内存为50G。。内存最后怎会使用到58了。全用完了。。。。。

    2011年11月8日 5:55
  • 如何设置才能防止出现这样的错误,Failed allocate pages: FAIL_PAGE_ALLOCATION 。SQL使用内存我都设置最大可使用的数值。。。另外说数据现在大小为72G。。。。应该要配置多大的内存合适?

     

    2011年11月8日 6:06
  • 假如CPU有很多CORE,如32 or 24,把max degree of parallelism设小如8或更小点,再观察一下

    当然,这会影响到单个查询的并行查询对多CORE的利用率

     

    多CORE需要DB设计上配合



    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2011年11月8日 6:27
  • 假如CPU有很多CORE,如32 or 24,把max degree of parallelism设小如8或更小点,再观察一下

    当然,这会影响到单个查询的并行查询对多CORE的利用率

     

    多CORE需要DB设计上配合



    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com


    其实我不是太明白 您说的查询并行度和内存不够分配有什么关联?

     

    2011年11月8日 6:36
  • 你是32位的还是64位的?
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年11月8日 7:06
  • 64位的
    2011年11月8日 7:49
  • 64位的


    那将SQL Server可用内存降低到45G,试试.

    sp_configure 'max server memory',46080

    go

    reconfigure

    go

    之后看下还会出现这个错误吗?

     


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年11月8日 8:22
  • 1,你设置的最大使用内存为50G只能代表BPOOL能够使用的最大内存,并不能代表SQL SERVER总体使用的内存量。 2,从'pMAX_CPU_PERCENT_10' 来看, 你似乎启动了resource governor, 如果是这样的话,你的resource governor是怎么设置的?
    2011年11月8日 9:35
  • 1,你设置的最大使用内存为50G只能代表BPOOL能够使用的最大内存,并不能代表SQL SERVER总体使用的内存量。 2,从'pMAX_CPU_PERCENT_10' 来看, 你似乎启动了resource governor, 如果是这样的话,你的resource governor是怎么设置的?

    'pMAX_CPU_PERCENT_10'资源池针对用户限制。。最大能使用CPU资源为10%,最大能使用内存为10% 。您刚刚说设置了50G并不代表SQL SERVER能使用的内存量,可是这台服务器除了运行SQL SERVER 没有运行其他东西,正常来说应该可以使用50G空间 
    2011年11月8日 10:23
  • "There is insufficient system memory in resource pool 'pMAX_CPU_PERCENT_10' to run this query."

     

    是System memory,非Data buffer不够,所以试下将max degree of parallelism改小


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2011年11月8日 13:39
  • You should monitor memory usage with perfmon. Do you have any ssis task running on the machine? SSIS may use lot of memory and need restart it to release memory. Regarding max dop, there's special consideration for numa processor. Should be equal or less than number of processors in each numa.
    2011年11月8日 14:27
  • 1,你设置的最大使用内存为50G只能代表BPOOL能够使用的最大内存,并不能代表SQL SERVER总体使用的内存量。 2,从'pMAX_CPU_PERCENT_10' 来看, 你似乎启动了resource governor, 如果是这样的话,你的resource governor是怎么设置的?

    'pMAX_CPU_PERCENT_10'资源池针对用户限制。。最大能使用CPU资源为10%,最大能使用内存为10% 。您刚刚说设置了50G并不代表SQL SERVER能使用的内存量,可是这台服务器除了运行SQL SERVER 没有运行其他东西,正常来说应该可以使用50G空间 

    1,不是这个意思,SQL SERVER 的内存由BPOOL+  extended stored procedures, linked servers, COM objects, and certain SQLCLR objects等其他对象组成的,所以即使你配置了最大使用内存为50G(BPOOL),而SQL SEREVER 却把58G的内存耗尽了的这种情况是可能发生的。

    2,另外resource governor的MIN值都是0吗?

    从你resource governor的设置来看,你设置的MAX为10%,会造成资源的浪费现象,这也意味你耗尽的只是'pMAX_CPU_PERCENT_10'那个POOL的5G的内存,而不是耗尽了全部发58G的内存。所以你可以把那个'pMAX_CPU_PERCENT_10'的MAX值设置大一点来看看,当然具体的设置你需要监控每个POOL的使用情况来设置。

    3,还有就是内存泄漏了,看一下下面的performance counters

    Process: Working Set
    SQL Server: Buffer Manager: Total Pages
    SQL Server: Memory Manager: Total Server Memory (KB)
    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

     

     


    2011年11月9日 2:05
  • Resourse governor can only limit query memory, it doesn't nothing on buffer pools.
    2011年11月9日 3:42
  • 没错,Resourse governor的限制不会造成上面的那个错误,只会让查询等待内存分配。
    想不想时已是想,不如不想都不想。
    2011年11月9日 6:34
    版主
  • 不过我在下面3个URL查到由于RG的限制而造成 “MSSQLSERVER_701”错误,我不知道是不是我理解错了?

    当然,这个transaction很可能不是发生错误的根本原因,另外,我也认为RG不会去控制BPOOL,而且我在回复中似乎也没有暗示到这点。

    当然这个帖子可能发帖的人都已经不关注,只是想确认一下自己的理解,望指教。

    1,“Advanced Troubleshooting

    The following conditions can cause this error:

    The memory pool reaches its total limit.”

    http://technet.microsoft.com/en-us/library/cc627395.aspx

     

    2,"701 - There is insufficient system memory in resource pool 'pool_name' to run "

     

    Check the out_of_memory_count column of the sys.dm_resource_governor_resource_pools DMV. If this count is localized to a particular resource pool, Resource Governor configuration is the most likely reason.

    http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

     

    3,“or memory limits on the current workload.”

    http://technet.microsoft.com/en-us/library/aa337311.aspx



    2011年11月10日 12:11
  • Those are valid for 701 error, but root issue OP had was server ran out memory. That's why we said RG shouldn't be the cause, have to find out processes that used 8gb memory. Page file size too small maybe one of the factors also.
    2011年11月10日 14:46
  • Those are valid for 701 error, but root issue OP had was server ran out memory. That's why we said RG shouldn't be the cause, have to find out processes that used 8gb memory. Page file size too small maybe one of the factors also.

    谢谢,不过没有明白OP是指代什么:),还有你说的 8gb memory是哪里来的?
    2011年11月10日 15:16
  • OP is original poster. OP's server is dedicated for sql (as OP said) and has 58gb memory, allocated 50gb to sql.
    2011年11月10日 15:51
  • OP is original poster. OP's server is dedicated for sql (as OP said) and has 58gb memory, allocated 50gb to sql.

    thank you :)
    2011年11月10日 15:58