Sql-server – Adding index – There is insufficient system memory in resource pool ‘default’ to run this query

memory-optimized-tablessql serversql-server-2017

Here's the query I'm trying to run in order to add an index to my new memory optimized table

ALTER TABLE [s_Dim_Response_ram]
ADD INDEX response_ram_clientsurveydid NONCLUSTERED ([Clientid],[SurveyDefID])

And here's the error that I get:

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

The table has 23 million rows in it, and both columns in the index I'm trying to add are ints.

The server has 32GB of ram on it, and SQL Server is set to use a max of 26GB of ram.

It was upgraded from SQL 2008 R2 to SQL Server 2017 over the weekend, so I could try to take advantage of Memory Optimized tables. I made a copy of an existing regular table schema but as a memory optimized, and then tried to add a index as shown above.

How do I fix this error? Do I need more memory for SQL Server? Or more free system memory? Are there settings I could change to help?

edit: I tried deleting all the rows from the table first, and then adding the index. I still get the same error with an empty table.

Here are my memory performance counters:

counter_name                                                    MB
Stolen Server Memory                                        15682.015625
Free Memory                                                 4106.617187
Lock Memory                                                 8.093750
Log Pool Memory                                             3.140625
Connection Memory                                           2.070312
Optimizer Memory                                            1.554687
Cursor memory usage                                         0.031250
Cursor memory usage                                         .031250
Cursor memory usage                                         0.000000
Granted Workspace Memory                                    0.000000

Here are my top 20 Clerks

    type                mb
Total               22085.913770
MEMORYCLERK_XTP         14733.703125
MEMORYCLERK_SQLBUFFERPOOL   6877.562500
CACHESTORE_SQLCP        104.601562
CACHESTORE_OBJCP        94.734375
MEMORYCLERK_SOSNODE     54.804687
MEMORYCLERK_SQLSTORENG      54.156250
CACHESTORE_PHDR         31.093750
USERSTORE_SCHEMAMGR     27.031250
MEMORYCLERK_SQLCLR      21.296875
MEMORYCLERK_SQLTRACE        17.179687
USERSTORE_DBMETADATA        13.648434
CACHESTORE_SYSTEMROWSET     13.539053
MEMORYCLERK_SQLGENERAL      8.132812
OBJECTSTORE_LOCK_MANAGER    8.093749
USERSTORE_TOKENPERM     5.077892
MEMORYCLERK_SQLLOGPOOL      3.140625
USERSTORE_OBJPERM       2.257797
MEMORYCLERK_XE          2.195312
MEMORYCLERK_SQLCONNECTIONPOOL   2.000000
MEMORYCLERK_SQLOPTIMIZER    1.593750

My SQL version is Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Best Answer

The problem is that the amount of memory available for memory-optimized data is not a fixed size. It depends on system needs.

Please see my post here: http://nedotter.com/archive/2018/01/in-memory-oltp-resources-part-4-oom-the-most-feared-acronym-in-all-of-in-memory-oltp/

Bottom line is that you need more memory.

Related Solutions

Sql-server – SQL Server 2008 R2 There is insufficient system memory in resource pool ‘default’ to run this query

You say you enabled AWE on SQL Server but did you activate /PAE in the boot.ini file?

PAE enables Large Memory support.

Activate PAE in Windows 32Bit Enterprise

c:\boot.ini

[boot loader]
timeout=30
default=multi(0)disk(2)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE

http://support.microsoft.com/kb/283037

Lock Pages in Memory

As part of enabling AWE, you would also need to give the SQL Server Service user the right to Lock Pages in Memory. Enabling this option in the Group Policy dialog (gpedit.msc), prevents Windows from paging memory out of the SQL Server working set for its own needs, especially if memory starts to run low on Windows.

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

Monitoring AWE Memory Usage

To test AWE usage, I setup a Windows 2003 Enterprise SP2 with PAE enabled in boot.ini. I used SQL Server 2005 32bit Standard SP4.

The total physical memory is 8192Mb. The max memory setting for SQL Server is configured at 8192 and the minimum is at 2048.

Does SQL Server see AWE on startup.

2012-02-09 07:27:51.35 Server Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

Is SQL Server using Locked Pages for the Buffer Pool

According to some blogs, I should be seeing this message, but I'm not.

Using locked pages for buffer pool. This is an informational message only; no user action is required.

Memory being allocated through AWE

SELECT  SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM    sys.dm_os_memory_clerks ;

[3648]

Memory allocated to Multi-Page memory.

select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks

[14]

Multi-page memory can not use AWE allocated memory

Memory being used outside the buffer pool

SELECT sum(multi_pages_kb 
 + virtual_memory_committed_kb + shared_memory_committed_kb) AS
 [Memory used outside BPool, mb]
FROM sys.dm_os_memory_clerks 
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL'

[24]

Overall memory allocation by component

SELECT type, (single_pages_kb)/1024 as Single_Pages_MB, (multi_pages_kb)/1024 
AS Multi_Pages_MB, (awe_allocated_kb)/1024 as AWE_allocated_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

This will show you how single-page and multi-page memory is being used by each component. It will also tell you which one is able to benefit from AWE.

Mutli-page : when request exceeds 8Kb

Single-page : when request is less than or equal to 8Kb

As a side note, I have found sp_whoisactive very helpful in finding slow queries, memory hogs and just about everything that's going on in SQL Server. Here is a link that Brent Ozar provides on how to set it up and use it.

http://www.brentozar.com/archive/tag/sp_whoisactive/

Sql-server – SQL Server Memory Clerk Description

There are 4 kinds of memory clerks in SQL Server.

Generic
Cache Store
Object Store
User store

memoryclerk_sqlgeneral is a general memory clerk responsible for a variety of non-specific sql activities whereas the memoryclerk_sqlbufferpool is the clerk dedicated solely to the buffer pool, since the buffer pool is a busy process.

Links:

SQLServer-DBA memory

MSDN Memory Clerks

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 There is insufficient system memory in resource pool ‘default’ to run this query

Sql-server – SQL Server Memory Clerk Description

Related Question