SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.
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?
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.
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
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.
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.
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
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.
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