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)