none
Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION RRS feed

  • 问题

  • Recently about once a month, one of our night scheduled job failed, and error message is : "Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION", followed by a lot of memorystatus messages.. I paste a few of the message at the bottom..

    The SQL is 64bit SQL 2012 Enterprise edtion SP1, clustered. Server has total 16GB RAM, Max memory is set at 14GB. Sql service account has lock pages in memory permission.

    When I checked SQL Server memory usage, found that "MEMORYCLERK_SQLCLR" reserves 12GB memory and "MEMORYCLERK_SQLBUFFERPOOL" only reserves 6GB.  But we have CLR disabled on the SQL, why CLR takes so much memory and won't release?

    Another finding is that page life expectancy suddenly drops to 0 every a few hours, and then climbs up gradually. I googled about this, and it seems like a bug with SQL 2012 SP1.


    So what my question is: what might have caused the buffer allocation failure? Any comments or advise is highly appreciated!! Thanks in advance!



    Process/System Counts                         Value
    ---------------------------------------- ----------
    Available Physical Memory                 724324352
    Available Virtual Memory                 8758264913920
    Available Paging File                    15874551808
    Working Set                               155299840
    Percent of Committed Memory in WS               100
    Page Faults                                  946806
    System physical memory high                       1
    System physical memory low                        0
    Process physical memory low                       0
    Process virtual memory low                        0
    2014-09-11 01:32:10.00 spid61      
    Memory Manager                                   KB
    ---------------------------------------- ----------
    VM Reserved                                36146920
    VM Committed                                 241916
    Locked Pages Allocated                     14441828
    Large Pages Allocated                        167936
    Emergency Memory                               1024
    Emergency Memory In Use                          16
    Target Committed                           14680072
    Current Committed                          14683744
    Pages Allocated                            13980136
    Pages Reserved                                 3104
    Pages Free                                    47256
    Pages In Use                                5815424
    Page Alloc Potential                        8130640
    NUMA Growth Phase                                 2
    Last OOM Factor                                   2
    Last OS Error                                     0
    2014-09-11 01:32:10.00 spid61      
    Memory node Id = 0                               KB
    ---------------------------------------- ----------


    2014年9月29日 2:27

答案

  • First of all, 2gb memory is not enough for 64-bit os. So should reduce max memory for sql to 12gb even smaller (we leave at least 6gb memory for os). Based on page life expectancy of your server, it needs more memory. Regarding clr, certain data type like hierarchyid triggers clr under the hood even you disabled it on the server.
    2014年9月29日 3:51
  • Keep in mind that sql agent and other sql services like ssis use memory outside of sql engine, that's why have to leave enough memory for OS and other non-sql engine processes.

    We have many sql2012 sp1 servers but didn't find sudden drop of PLE on them. You should find out total db size and biggest table size on the server, will see PLE drop if sql constantly load data to buffer which indicates memory shortage.

    For CLR memory usage, don't know any way to limit it manually. 

    2014年9月29日 14:32

全部回复

  • First of all, 2gb memory is not enough for 64-bit os. So should reduce max memory for sql to 12gb even smaller (we leave at least 6gb memory for os). Based on page life expectancy of your server, it needs more memory. Regarding clr, certain data type like hierarchyid triggers clr under the hood even you disabled it on the server.
    2014年9月29日 3:51
  • Thanks a lot rmiao for your kindly reply!! Really appreciated!

    I monitored our server performance counters for several business days, since avaliable MBytes is always above 200MB, so I though 2GB was fine for OS.. I will try modifying the max memory settings and leave more for OS.

    Regarding the page life expectancy, it is always above 1000, just sometimes drops suddenly from more than 1000  to 0, from my observation, it is very similar with a SP1 bug, so I am not sure if more RAM will help us.. I paste a typical case screenshot for your information. May I ask your kindly help to check below MS article and let me know your thought?

    http://support.microsoft.com/kb/2845380/en-us

    http://blogs.msdn.com/b/axinthefield/archive/2013/10/22/severe-performance-issue-in-sql-2012-with-service-pack-1-page-life-expectancy-drops-near-0.aspx

    The last thing, do you know if any way in SQL 2012 to limit CLR taking too much memory? 

    Thanks so much for your kindly help!

    2014年9月29日 4:39
  • Keep in mind that sql agent and other sql services like ssis use memory outside of sql engine, that's why have to leave enough memory for OS and other non-sql engine processes.

    We have many sql2012 sp1 servers but didn't find sudden drop of PLE on them. You should find out total db size and biggest table size on the server, will see PLE drop if sql constantly load data to buffer which indicates memory shortage.

    For CLR memory usage, don't know any way to limit it manually. 

    2014年9月29日 14:32
  • Keep in mind that sql agent and other sql services like ssis use memory outside of sql engine, that's why have to leave enough memory for OS and other non-sql engine processes.

    We have many sql2012 sp1 servers but didn't find sudden drop of PLE on them. You should find out total db size and biggest table size on the server, will see PLE drop if sql constantly load data to buffer which indicates memory shortage.

    For CLR memory usage, don't know any way to limit it manually. 

    Thanks so much rmiao! I get it, and I have modified the max server memory settings to leave OS 4GB. Another SQL used linked server to read and update data on this problematic SQL, does the linked server use memory outside of buffer pool and SQL engine? Thanks a lot!
    2014年9月30日 0:57
  • LS uses memory outside of sql engine.
    2014年9月30日 1:32
  • 我也是没有找到文献可以限制CLR 使用的内存

    2014年9月30日 2:36