none
There is insufficient system memory in resource pool 'internal' to run this query. RRS feed

  • Question

  • Hi

    I have a .NET application executing a stored procedure on a SQL Server 2008 SP1 64 bit instance (version 10.0.2531.0), and getting the following exception:

    There is insufficient system memory in resource pool 'internal' to run this query.

    Maximum server memory is set to 4096MB. The server has 4GB of physical RAM available, and is running Windows Server 2008 R2 x64.

    The SQL Server instance is only consuming approx 1.5GB RAM and there is 15+% physical RAM unused.

    The messages in error.log are as follows:

    2010-09-25 03:28:38.15 spid51    Failed allocate pages: FAIL_PAGE_ALLOCATION 1
    2010-09-25 03:28:38.15 spid51   
    Memory Manager                  KB
    ---------------------------------------- ----------
    VM Reserved                 4277792
    VM Committed                1532212
    Locked Pages Allocated              0
    Reserved Memory                1024
    Reserved Memory In Use              0
    2010-09-25 03:28:38.15 spid51   
    Memory node Id = 0                KB
    ---------------------------------------- ----------
    VM Reserved                 4276704
    VM Committed                1531184
    Locked Pages Allocated              0
    MultiPage Allocator              38992
    SinglePage Allocator            1371648
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLGENERAL (node 0)         KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              2280
    MultiPage Allocator              2360
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLBUFFERPOOL (node 0)        KB
    ---------------------------------------- ----------
    VM Reserved                 4214784
    VM Committed                1470976
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator               0
    MultiPage Allocator               400
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLQUERYEXEC (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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator            1330432
    MultiPage Allocator              16584
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLUTILITIES (node 0)        KB
    ---------------------------------------- ----------
    VM Reserved                   480
    VM Committed                  480
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              128
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLSTORENG (node 0)         KB
    ---------------------------------------- ----------
    VM Reserved                  2816
    VM Committed                  2816
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              3000
    MultiPage Allocator              4728
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLCONNECTIONPOOL (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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLCLR (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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SQLSERVICEBROKER (node 0)      KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              144
    MultiPage Allocator               544
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SNI (node 0)             KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator               8
    MultiPage Allocator               16
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_BHF (node 0)             KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              1048
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_HOST (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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_SOSNODE (node 0)           KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              4304
    MultiPage Allocator              11248
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_FSAGENT (node 0)           KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator               8
    MultiPage Allocator               16
    2010-09-25 03:28:38.15 spid51   
    MEMORYCLERK_XE (node 0)             KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator               72
    MultiPage Allocator               168
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_OBJCP (node 0)            KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              1544
    MultiPage Allocator               152
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_SQLCP (node 0)            KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              1272
    MultiPage Allocator              1568
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_PHDR (node 0)             KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              9568
    MultiPage Allocator               80
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_XPROC (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
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_TEMPTABLES (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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_XMLDBTYPE (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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_BROKERTBLACS (node 0)         KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator               40
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_SYSTEMROWSET (node 0)         KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              120
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    CACHESTORE_CONVPRI (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
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    USERSTORE_SCHEMAMGR (node 0)           KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator             10952
    MultiPage Allocator               256
    2010-09-25 03:28:38.15 spid51   
    USERSTORE_DBMETADATA (node 0)          KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              2672
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    USERSTORE_TOKENPERM (node 0)           KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              328
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    USERSTORE_OBJPERM (node 0)            KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              520
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    USERSTORE_SXC (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
    2010-09-25 03:28:38.15 spid51   
    OBJECTSTORE_LBSS (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
    2010-09-25 03:28:38.15 spid51   
    OBJECTSTORE_SNI_PACKET (node 0)         KB
    ---------------------------------------- ----------
    VM Reserved                    0
    VM Committed                   0
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator               80
    MultiPage Allocator               56
    2010-09-25 03:28:38.15 spid51   
    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
    2010-09-25 03:28:38.15 spid51   
    OBJECTSTORE_LOCK_MANAGER (node 0)        KB
    ---------------------------------------- ----------
    VM Reserved                  4096
    VM Committed                  4096
    Locked Pages Allocated              0
    SM Reserved                    0
    SM Committed                   0
    SinglePage Allocator              1808
    MultiPage Allocator                0
    2010-09-25 03:28:38.15 spid51   
    Buffer Pool                  Value
    ---------------------------------------- ----------
    Committed                  180480
    Target                    180480
    Database                    8974
    Dirty                      367
    In IO                       0
    Latched                      0
    Free                       50
    Stolen                    171456
    Reserved                     0
    Visible                   180480
    Stolen Potential                 0
    Limiting Factor                 12
    Last OOM Factor                  0
    Page Life Expectancy              607
    2010-09-25 03:28:38.15 spid51   
    Process/System Counts             Value
    ---------------------------------------- ----------
    Available Physical Memory        1106481152
    Available Virtual Memory         8791447781376
    Available Paging File          5191540736
    Working Set               1557618688
    Percent of Committed Memory in WS        99
    Page Faults                106802455
    System physical memory high            1
    System physical memory low            0
    Process physical memory low            1
    Process virtual memory low            0
    2010-09-25 03:28:38.15 spid51   
    Procedure Cache                Value
    ---------------------------------------- ----------
    TotalProcs                    1
    TotalPages                   1776
    InUsePages                    1
    2010-09-25 03:28:38.15 spid51   
    Global Memory Objects             Pages
    ---------------------------------------- ----------
    Resource                    342
    Locks                      226
    XDES                       27
    SETLS                       4
    SE Dataset Allocators               8
    SubpDesc Allocators                4
    SE SchemaManager                660
    SE Column Metadata Cache            732
    SQLCache                    214
    Replication                    2
    ServerGlobal                   50
    XP Global                     2
    SortTables                    2
    2010-09-25 03:28:38.15 spid51   
    Query Memory Objects (internal)        Value
    ---------------------------------------- ----------
    Grants                      0
    Waiting                      0
    Available                   3590
    Current Max                  3590
    Future Max                   3590
    Physical Max                 134975
    Next Request                   0
    Waiting For                    0
    Cost                       0
    Timeout                      0
    Wait Time                     0
    2010-09-25 03:28:38.15 spid51   
    Small Query Memory Objects (internal)     Value
    ---------------------------------------- ----------
    Grants                      0
    Waiting                      0
    Available                    640
    Current Max                   640
    Future Max                   640
    2010-09-25 03:28:38.15 spid51   
    Optimization Queue (internal)         Value
    ---------------------------------------- ----------
    Overall Memory              1185103872
    Target Memory               978927616
    Last Notification                 2
    Timeout                      6
    Early Termination Factor             5
    2010-09-25 03:28:38.15 spid51   
    Small Gateway (internal)           Value
    ---------------------------------------- ----------
    Configured Units                 16
    Available Units                 15
    Acquires                     1
    Waiters                      0
    Threshold Factor               380000
    Threshold                  380000
    2010-09-25 03:28:38.15 spid51   
    Medium Gateway (internal)           Value
    ---------------------------------------- ----------
    Configured Units                 4
    Available Units                  3
    Acquires                     1
    Waiters                      0
    Threshold Factor                 12
    Threshold                 81577301
    2010-09-25 03:28:38.15 spid51   
    Big Gateway (internal)            Value
    ---------------------------------------- ----------
    Configured Units                 1
    Available Units                  0
    Acquires                     1
    Waiters                      0
    Threshold Factor                 8
    Threshold                 122365952
    2010-09-25 03:28:38.15 spid51   
    Memory Pool Manager              Pages
    ---------------------------------------- ----------
    Reserved Current                 0
    Reserved Limit                  2
    2010-09-25 03:28:38.15 spid51   
    Memory Pool (internal)            Pages
    ---------------------------------------- ----------
    Allocations                 171454
    Predicted                  208395
    Private Target                  0
    Private Limit                   0
    Total Target                 171456
    Total Limit                 171456
    OOM Count                   30141
    2010-09-25 03:28:38.15 spid51   
    MEMORYBROKER_FOR_CACHE (internal)       Pages
    ---------------------------------------- ----------
    Allocations                  2512
    Rate                       -6
    Target Allocations               4520
    Future Allocations                0
    Overall                   144666
    Last Notification                 0
    2010-09-25 03:28:38.15 spid51   
    MEMORYBROKER_FOR_STEAL (internal)       Pages
    ---------------------------------------- ----------
    Allocations                 168942
    Rate                      3198
    Target Allocations              119498
    Future Allocations                0
    Overall                   144666
    Last Notification                 2
    2010-09-25 03:28:38.15 spid51   
    MEMORYBROKER_FOR_RESERVE (internal)      Pages
    ---------------------------------------- ----------
    Allocations                    0
    Rate                       0
    Target Allocations               4520
    Future Allocations              33743
    Overall                   144666
    Last Notification                 0
    2010-09-25 03:28:38.21 spid51   Error: 701, Severity: 17, State: 123.
    2010-09-25 03:28:38.21 spid51   There is insufficient system memory in resource pool 'internal' to run this query.
    

    Any help appreciated - really don't know where to go from here.

    Thanks

    Tom

    Saturday, September 25, 2010 9:36 AM

All replies

  • Are you using resource governor?

    I would definetely set Maximum Server Memory smaller if you have 4GB of phisical memory, set Sql Server to Maximum 3GB, btw if from your obbservation Sql Server consumes 1.5GB maybe you can restrict it to 2GB.

    You should also consider using -g startup parameter which would enable to reserve more memory than default 256MB for memory outside the pool.

    http://msdn.microsoft.com/en-us/library/ms190737.aspx


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Saturday, September 25, 2010 10:26 AM
  • Hi Krystian

    Resource governor is disabled.

    I have tried different values for Maximum Server Memory, from the default (very high number) down to 2GB.

    I thought the -g startup parameters were only useful for 32 bit instances of SQL Server - is this incorrect? Also the page you referenced states:

    Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:

    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"

    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

       

    I am not seeing either of those messages in the error log.

    Thanks
    Tom

    Saturday, September 25, 2010 11:39 AM
  • Hi Tom,

    MemToLeave, -g switches and such are pretty irrelevant on 64-bit SQL Server.

    CSS SQL Server Engineers Blog Post

    64-bit SQL Server has 8TB of VAS (virtual address space) to play with on x64, 7TB on Itanium. Even on a server with 512GB RAM - yes GB - the buffer pool uses 0.5TB of VAS. That leaves at least 6.5TB of VAS for other stuff!

    The point of the -g switch was to reserve a certain amount of VAS, before thread stack space, because VAS was so tight on the 32-bit architecture. The default of 256MB (never been any different) ends up using 384MB of VAS once 0.5MB per thread for stack space has been reserved (this varies a bit depending on configuration, but it's about right).

    So, there's absolutely no need to use -g on 64-bit platforms, and as far as I know, it is ignored.

    If you're seeing problems like reported earlier in this thread, it is most likely because of something outside of SQL Server's control leaking memory. Prime suspects are extended stored procedures - deprecated for good reason! Given effectively unlimited VAS, extended stored procedures with leaks, and other memory-related bugs, are more likely to cause problems, not less.

    HTH


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ Please mark "Propose As Answer" if my answer helped..... :)
    • Edited by GURSETHI Saturday, September 25, 2010 2:04 PM addition of idea
    Saturday, September 25, 2010 2:03 PM
  • Hi Gursethi

    I thought this was the case.

    No extended stored procedures are in use here. Is there any way to narrow this down further?

    I have spun up the same task on an Amazon EC2 instance with 17GB of RAM to see if this is something specific to our own setup.

    Thanks
    Tom

    Saturday, September 25, 2010 10:34 PM
  • On that EC2 instance, I am now getting the following error:

    Error: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

    Okay, so the stored procedure is large and complex, but I wouldn't have thought it is THAT complex...!

    Has anyone seen this before? Is there any choice but to change the design?

    Saturday, September 25, 2010 10:52 PM
  • Strange ....the error in your first post is related to M2L pressure ...but yours is a 64 bit server and 64 bit SQL ..could you please post the errorlog as well as select @@version output here ... I have seen some bugs rasied at connect which were either fixed or not reproducilbe ..but there also people have raised when SQL Server was x64 bit ....

    Referance :https://connect.microsoft.com/SQLServer/feedback/details/484787/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query?wa=wsignin1.0#

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, September 26, 2010 12:37 PM
  • could you please check : http://support.microsoft.com/kb/982376 in relation to your last reply ..


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, September 26, 2010 12:43 PM
  • Hi,

    I think the post which Abhay has mentioned above make close relation to the original problem. What I must suggest is try open a case with MS and see what they say. Reason for mentioning this is that it will be fast also we will come to know what exactly it is. Must say try to deliver MS engineer these posts which Abhay mentioned.

    I will request to reply once you get something concreate from MS.


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ Please mark "Propose As Answer" if my answer helped..... :)
    Sunday, September 26, 2010 2:14 PM
  • Damn, I forgot to get the error logs off the high-memory EC2 box before I deleted the instance.

    (The error logs for the first message ("There is insufficient system memory in resource pool 'internal' to run this query.") are in the first post)

    I will get the @@version shortly.

    Sunday, September 26, 2010 8:07 PM
  • The output of select @@version is:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) 

    I note this is pre 10.00.2789.00, which is the version number for Cumulative Update 9. I presume therefore that Amazon haven't applied it to their machine images. I will apply it myself now and do another run overnight, see if the same thing happens.

    Sunday, September 26, 2010 8:43 PM
  • Hi Gursethi

    I have never opened a support case with Microsoft before. Can you tell me what the process is please?

    Thanks
    Tom

    Sunday, September 26, 2010 8:50 PM
  • You can find it at :

    https://support.microsoft.com/oas/default.aspx?st=1&as=1&as=1&tzone=-330&gprid=13165&timestmp=634211387194015590&ps=1&acty=ProductList&ctl=productlist&wf=PID&trl=PID%7eProductList&sd=gn&c=SMC&ln=en-us&prid=12426&gsaid=541815

    Select the payment options ....yes the support is cost based :)


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, September 27, 2010 6:02 AM