2

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)

  • before adding index .. can you update stats with fullscan of that empty table and then try ? – Kin Shah Jun 25 '18 at 20:09
1

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.

| improve this answer | |
  • I had this running on a server with 16GB of ram until I had it upgraded to a new AWS instance with 32GB of ram at the same time it was upgraded to SQL Server 2017. Seems odd an extra 16GB of ram would not be enough to add an index to an empty memory optimized table with no data in it..? – user1308743 Jun 25 '18 at 20:04
  • Yeah, I've complained to Microsoft that this is an issue - that committed_target_kb does not tell the entire truth - but so far nothing has changed. But I've got another question for you - why do you think In-Memory OLTP will make your system faster? – NedOtter Jun 25 '18 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.