Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Index Create Memory Setting for SQL Server

MSSQLTips author Manvendra Singh By:   |   Read Comments (7)   |   Related Tips: More > 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


Last Update: 12/5/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, December 05, 2012 - 3:36:09 AM - Ganesh Jagdale Read The Tip

Thanks


Manvendra

 Memory setting this is very very importent cunsept...


Wednesday, December 05, 2012 - 9:43:34 AM - Viplove Desai Read The Tip

Very helpful!!

Cheers.


Wednesday, December 05, 2012 - 10:45:51 AM - Satinder Thakur Read The Tip

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 05, 2012 - 11:12:24 AM - Sandeep Kataria Read The Tip

Manvendra can i hire you? write to me at amukataria@gmail.com

 

sandeep

 


Tuesday, January 22, 2013 - 11:23:09 AM - Jay Read The Tip

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


Friday, September 27, 2013 - 6:35:38 AM - Ramesh Read The Tip

 

It worked for me, Thanks for the information


Thursday, August 14, 2014 - 11:19:12 AM - Kaleem Read The Tip

Thanks Manvendra, very well explained.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.