none
insufficient memory in resource pool 'PoolCIMWork' RRS feed

  • Question

  • SQL Server 2016 SP1 build 13.0.4451.0

    Server has 64 Gig of memory, SQL Server gets 58 Gig.

    With small resource pool 'MyResourcePool' @1% for both min & max I'm getting the OOM condition:

    Resource governor ON

    There are several small MO tables (max 6K records each) that are transferred hourly from MO to permanent (partitioned) storage.  The OOM condition happens on the insert to the MO tables.

    Pool memory query:

    WITH    cte
      AS ( SELECT   RP.pool_id ,
      RP.Name ,
      RP.min_memory_percent ,
      RP.max_memory_percent ,
      CAST (RP.max_memory_kb / 1024. / 1024. 
        AS NUMERIC(12, 2)) AS max_memory_gb ,
      CAST (RP.used_memory_kb / 1024. / 1024. 
        AS NUMERIC(12, 2)) AS used_memory_gb ,
      CAST (RP.target_memory_kb / 1024. / 1024. 
        AS NUMERIC(12,2)) AS target_memory_gb,
      CAST (SI.committed_target_kb / 1024. / 1024. 
        AS NUMERIC(12, 2)) AS committed_target_kb 
        FROM     sys.dm_resource_governor_resource_pools RP
        CROSS JOIN sys.dm_os_sys_info SI
      )
    SELECT  c.pool_id ,
      c.Name ,
      c.min_memory_percent ,
      c.max_memory_percent ,
      c.max_memory_gb ,
      c.used_memory_gb ,
      c.target_memory_gb ,  
      CAST(c.committed_target_kb  *
      CASE WHEN c.committed_target_kb <= 8 THEN 0.7
        WHEN c.committed_target_kb < 16 THEN 0.75
        WHEN c.committed_target_kb < 32 THEN 0.8
        WHEN c.committed_target_kb <= 96 THEN 0.85
        WHEN c.committed_target_kb > 96 THEN 0.9
      END * c.max_memory_percent /100 AS NUMERIC(12,2))
       AS [Max_for_InMemory_Objects_gb]
    FROM    cte c

    returns:

    1    internal    0    100    53.20    2.29    53.20    47.60
    2    default    0    100    52.67    0.10    52.67    47.60
    259    PoolCIMWork    1    1    0.53    0.05    0.53    0.48

    I'll paste a link to the complete error log trace later, but here's some of them:

    Date        9/25/2017 5:08:29 PM
    Log        SQL Server (Current - 9/25/2017 5:09:00 PM)

    Source        spid67

    Message
    MEMORYBROKER_FOR_XTP (PoolCIMWork)               KB
    ---------------------------------------- ----------
    Allocations                                   31600
    Rate                                          -2784
    Target Allocations                            26520
    Future Allocations                                0
    Overall                                       26520
    Last Notification                                 2

    Date        9/25/2017 5:08:29 PM
    Log        SQL Server (Current - 9/25/2017 5:09:00 PM)

    Source        spid67

    Message
    MEMORYBROKER_FOR_STEAL (PoolCIMWork)             KB
    ---------------------------------------- ----------
    Allocations                                       0
    Rate                                              0
    Target Allocations                              824
    Future Allocations                                0
    Overall                                       26520
    Last Notification                                 0

    Date        9/25/2017 5:08:29 PM
    Log        SQL Server (Current - 9/25/2017 5:09:00 PM)

    Source        spid67

    Message
    MEMORYBROKER_FOR_CACHE (PoolCIMWork)             KB
    ---------------------------------------- ----------
    Allocations                                       0
    Rate                                              0
    Target Allocations                              824
    Future Allocations                                0
    Overall                                       26520
    Last Notification                                 0

    Date        9/25/2017 5:08:29 PM
    Log        SQL Server (Current - 9/25/2017 5:09:00 PM)

    Source        spid67

    Message
    Memory Pool (PoolCIMWork)                        KB
    ---------------------------------------- ----------
    Allocations                                   31600
    Predicted                                     31600
    Private Target                                31432
    Private Limit                                 31432
    Total Target                                  31432
    Total Limit                                  557840
    OOM Count                                         0



    • Edited by mannsjp64 Monday, September 25, 2017 10:50 PM cleanup
    Monday, September 25, 2017 10:49 PM

All replies

  • Hi mannsjp64,

    >> The OOM condition happens on the insert to the MO tables.

    Could you please post the detailed error message so we can have a better understanding about the issue?

    >> With small resource pool 'MyResourcePool' @1% for both min & max I'm getting the OOM condition

    1% maximum memory(0.53GB) for a resource pool is relatively low value to me, is there any specific reason for you to set such low value?

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 26, 2017 6:33 AM
  • Sadly, the client application truncates the error message so all I see is 'insufficient memory in resource pool 'PoolC<truncated> - it doesn't give me a number at all like 701 which would be helpful.

    The inserts into the transfer tables that were triggering this error have B-tree PKs on an identity column (sequence_number).  When transferring records, I look for the max current sequence number, copy everything with sequence number <= to it, then delete them from the table (leaving records inserted in the meantime).  I see rapid, unbounded growth in the 'Index Unused Memory' when looking at report 'Memory Usage by Memory Optimized Objects' - i.e. up to 250 MB at times before I get the OOM errors on tables that utilize only 5 MB.

    I've removed the B-Tree indexes and replaced them with hash indexes - useless for this application but now there's no index memory growth as bucket size is fixed.  Performance fine, as rowcount for these tables less than 10,000 and MO table scans are quick.

    Appears to me there's some config setting I'm missing that would help manage the Index Unused Memory better.

    I'll see if I can get a script put together to demonstrate.

    Thursday, September 28, 2017 6:42 PM
  • In one SSMS query window put in this:

    USE [master]
    GO
    
    /****** Object:  ResourcePool [PoolSQLShackDemo]    Script Date: 9/28/2017 3:11:41 PM ******/
    CREATE RESOURCE POOL [PoolSQLShackDemo] WITH(
    		min_memory_percent=1, 
    		max_memory_percent=1, 
    )
    GO
    
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO
    
    USE SQLShackDemo 
    GO
    ALTER DATABASE SQLShackDemo ADD FILEGROUP MemoryOpt_FG CONTAINS MEMORY_OPTIMIZED_DATA   
    ALTER DATABASE SQLShackDemo ADD FILE (name='MemoryOptDataF',filename='D:\MSSQL2016\Data\MemoryOptDataF') TO FILEGROUP MemoryOpt_FG   
    ALTER DATABASE SQLShackDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF  
    GO
    
    EXEC sp_xtp_bind_db_resource_pool 'SQLShackDemo', 'PoolSQLShackDemo'  
    GO
    
    USE master  
    GO  
    
    ALTER DATABASE SQLShackDemo SET OFFLINE  
    GO  
    ALTER DATABASE SQLShackDemo SET ONLINE  
    GO  
    
    USE SQLShackDemo 
    GO
    
    CREATE TABLE [MemoryOptTempTable]
    (
     [ID] INT IDENTITY(1,1) NOT NULL ,
     [First_Name] nvarchar(10) NULL,
     [Last_Name] nvarchar(10) NULL,
     
     CONSTRAINT [PK_MemoryOptTempTable]  PRIMARY KEY NONCLUSTERED 
    (
     ID ASC
    )
    )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
    GO
    
     
    create PROCEDURE dbo.MemoryOptTableTest --- Memory Optimized Temp Table
    AS
    BEGIN
    SET NOCOUNT ON;
     INSERT INTO MemoryOptTempTable (First_Name,Last_Name) VALUES ('Satya','Nadella')
    END
    GO
    
    exec dbo.MemoryOptTableTest 
    go 1000000
    
    
    

    And in another enter this:

    delete from dbo.MemoryOptTempTable where id <= (select max(ID) from dbo.MemoryOptTempTable);

    Do the delete periodically while the go 1000000 batch is running & monitor memory usage & batch insert output windows.  After awhile I'll get this error:

    Msg 41805, Level 16, State 109, Procedure MemoryOptTableTest, Line 5 [Batch Start Line 56]
    There is insufficient memory in the resource pool 'PoolSQLShackDemo' to run this operation on memory-optimized tables. See 'http://go.microsoft.com/fwlink/?LinkID=614951' for more information.

    even when there's plenty of memory available, and after deleting all records there will be a large chunk of Index Unused Memory left over.

    Tried this with MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT both ON & OFF with same result.

    Friday, September 29, 2017 5:07 PM
  • Hi mannsjp64,

    We are currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 4, 2017 7:07 AM
  • Hi mannsjp64,

    >> With small resource pool 'MyResourcePool' @1% for both min & max I'm getting the OOM condition

    Is there any special reason for you to allocate such small value of MAX_MEMORY_PERCENT? Could you please try to increase the value of MAX_MEMORY_PERCENT (For example 70%) to check if this error will occur or not?

    As there could be many possible causes of this issue, would you please do the following and upload all the results to one drive and share the link here so we can better understand your situation?

    1. Check to see your SQL Server error log under %ProgramFiles%\Microsoft SQL Server\MSSQLxx.InstanceName\MSSQL\Log;

    1.  Run the following command on SQL Server and save the result to one excel file and upload it to onedrive;

    USE master;

    GO

    EXEC sp_configure 'show advanced option', '1';

    RECONFIGURE;

    EXEC sp_configure;

    1. Create a folder: D:\PerfMonLogs\
      1. Configuration CMD: (run it in Command Prompt as Administrator)

    Default Instance:

    Logman create counter msperf -f bin -c  "\SQLServer:Buffer Manager\*" "\SQLServer:Memory Node(*)\*" "\SQLServer:Buffer Node(*)\*" "\SQLServer:Locks(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Database Mirroring(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Latches\*" "\SQLServer:Access Methods\*" "\SQLServer:SQL Statistics\*" "\SQLServer:Memory Manager\*" "\SQLServer:Wait Statistics(*)\*" "\LogicalDisk(*)\*" "\PhysicalDisk(*)\*" "\Processor(*)\*" "\Process(*)\*" "\Memory\*" "\System\*" -si 00:00:05 -o D:\PerfMonLogs\MS_perf_log.blg -cnf 24:00:00 -max 500

     Named Instance:

    Logman create counter msperf -f bin -c "\MSSQL$InstanceName:Buffer Manager\*" "\MSSQL$InstanceName:Memory Node(*)\*" "\MSSQL$InstanceName:Buffer Node(*)\*" "\MSSQL$InstanceName:Locks(*)\*" "\MSSQL$InstanceName:Databases(*)\*" "\MSSQL$InstanceName:Database Mirroring(*)\*" "\MSSQL$InstanceName:General Statistics\*" "\MSSQL$InstanceName:Latches\*" "\MSSQL$InstanceName:Access Methods\*" "\MSSQL$InstanceName:SQL Statistics\*" "\MSSQL$InstanceName:Memory Manager\*" "\MSSQL$InstanceName:Wait Statistics(*)\*" "\LogicalDisk(*)\*" "\PhysicalDisk(*)\*" "\Processor(*)\*" "\Process(*)\*" "\Memory\*" "\System\*" -si 00:00:05 -o D:\PerfMonLogs\MS_perf_log.blg  -cnf 24:00:00 -max 500

     Note: you can change the destination path for another larger space.

      1. Start CMD:

       Logman start msperf (Reproduce this issue)

      1. Stop CMD:

                      Logman stop msperf

     Please compress and upload all the .blg files from "D:\PerfMonLogs\"

    If you have any questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Edited by Hannah Yu Wednesday, October 11, 2017 9:29 AM
    Wednesday, October 11, 2017 9:24 AM
  • Our organization security policies don't allow OneDrive, dropbox, or most other online 3rd party storage services.  I can likely upload the .blg & other files to a location you specify.
    Wednesday, October 11, 2017 4:00 PM
  • I've been unable to re-replicate the failures seen on script shown above (even with the 1% MAX_MEMORY_PERCENT).  There have been no OS reboots since the failure was reported.

    The 1% MAX_MEMORY_PERCENT pool limit for MOTs on this server is 500 Meg of memory - still several times the maximum amount required to service the current needs of the memory-optimized transfer tables that are purged every hour. 

    I first saw this failure with MO tables using the 'default' pool which is much larger than 'CIMWorkPool'.  Was trying to replicate and further isolate the 'insufficient memory' issue more quickly with the smallest pool possible.

    Wednesday, October 11, 2017 7:17 PM
  • Hi mannsjp64,

    >> Our organization security policies don't allow OneDrive, dropbox, or most other online 3rd party storage services.  I can likely upload the .blg & other files to a location you specify.

    In this case, would you please upload the performance monitor and sp_configure to my workspace? That is very important for our analysis. We need to check the Memory, I/O speed, CPU usage and these information is logged in performance monitor and sp_configure.

     

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Hannah Yu Thursday, October 12, 2017 9:19 AM
    Thursday, October 12, 2017 9:17 AM