Index Create Memory Setting for SQL Server

By:   |   Comments (7)   |   Related: > Indexing


Problem

Today, a developer reported that he is not able to create an index in our development environment and is getting this error:

Msg 8606, Level 17, State 1, Line 1 This index operation requires 1024 KB of memory per DOP. The total requirement of 2048 KB for DOP of 2 is greater than the sp_configure value of 1000 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.

What does this error mean and how can this be fixed?

Solution

Once you look at the error, the initial impression may be to: change the "index create memory (KB)" settings in sp_configure or change the DOP value to resolve this issue. But where are these values set and what happens when you make these changes?  Before we make any changes, let's understand these server level settings to avoid any issues with other operations on the SQL Server instance, because these changes are instance level changes.

NOTE: DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER-LIFE CYCLE ENVIRNOMENTS SUCH AS DEVELOPMENT, TEST and QA

Index Create Memory Setting

The "index create memory" option in sp_configure advance server settings, controls the maximum amount of memory initially allocated for creating indexes. The default value for this option is 0 which is self-managed by SQL Server.

Degree of Parallelism Setting

DOP stands for "degree of parallelism" which can be used to run transactions in a parallel mode. We can set this value to limit the number of CPUs a particular process uses when it executes. We can set the "max degree of parallelism" for the entire instance. This means that any given step in any given query will not be assigned more CPU schedulers than the value set in that configuration option. Also, queries with a high degree of parallelism generally require more memory. If several concurrently running complex queries each execute in parallel with a degree of parallelism that is greater than 1, the memory requirement may be significant because each CPU scheduler will require at least the minimum memory to run that transaction which is assigned to that scheduler.

Our Example Problem

On our server, the DOP setting is set to 2, so when the developer was trying to create an index, his transaction is trying to run in parallel and split the operation into two parts.  The index operation runs on 2 CPU schedulers because of the DOP setting and each CPU scheduler requires the same amount of memory which is set in the "min memory per query (KB)" in sp_configure which is set to 1024 KB on our server. So the operation fails, because our "index create memory (KB)" setting is set to 1000 KB, which is less than the total required memory (2*1024) to process the create index operation.

Best Practice

The best practice is not to change the "index create memory (KB)" setting from its default value of 0, because by default SQL Server dynamically manages the amount of memory allocated for index creation operations. If additional memory is needed for creating indexes and the memory is available based on the server memory configuration settings, the server will allocate additional memory for index creation operations. If additional memory is needed but not available, index creation will use the memory already allocated to perform the index creation. However, if you do experience difficulties creating indexes, consider increasing the value of this option.

Changing Index Create Memory Using SSMS

Step 1

Connect to your SQL Server instance in SQL Server Management Studio. Right click on your connected SQL Server instance and choose Properties as per the screen shot below.

Right click on connected SQL Server Instance

Step 2

Click on the Memory page in the left pane. Your memory property page will appear like the screenshot below. Here you can see our "Index creation memory" option is set to 1000. I can change the DOP setting as well on the Advanced page to fix this issue, but changing the DOP value may impact other performance issues for the entire SQL Server instance.

Display Memory tab page

Step 3

Since our DOP setting is set to 2, we will set our index create memory value to 2048.  This is determined by (minimum memory per query (in KB) * DOP setting).  So if we look at the screen shot below our minimum memory per query is set to 1024 and our DOP setting is 2, so the value is 1024 * 2 = 2048.  If we want SQL Server to dynamically manage the index creation memory we would set the value to 0.

Change Index create memory value

Changing Index Create Memory Using TSQL

We can also set this value through sp_configure as well. Run the below code to set this value as per your requirement. You can verify this value by running sp_configure again once you set the appropriate value.

In this example we are setting the value to 2048.  If we want SQL Server to dynamically manage this we would set the value to 0.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'index create memory', 2048
GO
RECONFIGURE;
GO

Next Steps
  • Be careful while making any change in any value in sp_configure. This could impact your complete SQL Server instance.
  • For the most part you should let SQL Server manage this value for you, so use the default of 0.
  • Read more tips on SQL Server Indexing


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, August 14, 2014 - 11:19:12 AM - Kaleem Back To Top (34135)

Thanks Manvendra, very well explained.


Friday, September 27, 2013 - 6:35:38 AM - Ramesh Back To Top (26969)

 

It worked for me, Thanks for the information


Tuesday, January 22, 2013 - 11:23:09 AM - Jay Back To Top (21621)

Excellent article on memory. please read below link.

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

Thanks


Wednesday, December 5, 2012 - 11:12:24 AM - Sandeep Kataria Back To Top (20766)

Manvendra can i hire you? write to me at [email protected]

 

sandeep

 


Wednesday, December 5, 2012 - 10:45:51 AM - Satinder Thakur Back To Top (20764)

Great Job! Manvendra – Nice resolution but it may create problem in case of any further change in DOP configuration (>2) .Therefore the permanent resolution should be the default value 0 for Index create memory option.


Wednesday, December 5, 2012 - 9:43:34 AM - Viplove Desai Back To Top (20762)

Very helpful!!

Cheers.


Wednesday, December 5, 2012 - 3:36:09 AM - Ganesh Jagdale Back To Top (20754)

Thanks


Manvendra

 Memory setting this is very very importent cunsept...















get free sql tips
agree to terms