By: Manvendra Singh | 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:
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.
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.
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.
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
About the author
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
