Introduction
The Index Create Memory setting is an advanced SQL Server configuration setting which the SQL Server database engine uses to control the amount of maximum memory which can be allocated towards the creation of an index. In this article, we will take a look at the steps to resolve the below mentioned error message.
Error Message
You may start seeing this message in cases where the configured value for “index create memory (KB)” setting is lower than the required amount of memory required for the creation of index.
What is the Index Create Memory Setting and why is it important?
The Index Create Memory option is an advanced configuration setting which controls the amount of maximum memory that can be initially allocated to a task during index creation.
Permissions required to change server level settings
In SQL Server, the execute permission is granted (by default) to the sp_configure system stored procedure, with no parameter or just with the initial parameter to all the users. However, a user must be granted ALTER SETTINGS server level permission to execute sp_configure in order to be able to change the existing configuration value to new one and to execute RECONFIGURE statement.
The ALTER SETTINGS permission is available only to the members of SERVERADMIN or SYSADMIN fixed server roles.
Key limitations and restrictions for Index Create Memory settings
While making change to Index Create Memory advanced setting you need to always make sure that value Index Create Memory (KB) is greater than Min Memory per Query (KB) setting else you will receive a warning message even though the value is configured.
The value specified for Index Create Memory should never surpass the actual amount of memory which is available on the SQL Server.
The minimum memory requirements for index creation can increase if you are using partitioned tables and indexes. This generally happens if there are non aligned partitioned indexes and you also have configured high degree of parallelism (MAXDOP).
Important Note: Once you change the index create memory value in SQL Server the settings will come into effect immediately. Hence it is not required to recycle the SQL Server Database Engine Service. However, you must always be very careful while making changes to the advanced settings using the sp_configure system stored procedure or using Server Properties as these changes affect the overall performance of a SQL Server Instance.
Recommended best practice for configuring Index Create Memory advanced setting
The index create memory setting is an advanced configuration setting which can be used to control the amount of maximum memory which can be allocated for the creation of an index . It is highly recommended not to change the default value for “index create memory (KB)” advanced setting. The default value for “index create memory” advanced setting is ZERO which means SQL Server will dynamically manage the memory based on the requirement.
If SQL Server requires additional memory for index creation; and if the memory is available to the database engine, then the SQL Server will use it. However, if memory is not available additionally, then during such sceneries the index creation will continue with the currently allocated memory. In case if you have started experiencing difficulties with the current settings then consider increasing the value for “index create memory (KB)” advanced setting.
Let us go through the step by step approach to make changes to Index Create Memory Setting in SQL Server Using SQL Server Management Studio or executing the sp_configure system stored procedure. You don’t need to restart the SQL Server Database Engine Service after changing the index create memory advanced server setting, as this setting comes into effect immediately.
How to change the Index Create Memory setting using SSMS
Open SQL Server Management Studio and then connect to SQL Server Instance.
In Object Explorer, right click the SQL Server Instance and click on Properties as shown in the snippet below.
In Server Properties window, click on Memory node under select a page option on the left side pane as shown in the snippet below. Here, you could see that the configured value for Index Creation Memory (in KB) is 768.
Error Message
Based on above mentioned error message, we can either set the value for Index Creation Memory (in KB) as 2048 i.e., twice the value of 1024 KB. The reason being on this SQL Server Instance the Max Degree of Parallelism (MAXDOP) is set to 2.
Finally, click OK to save the new changes.
Recommended best practice for Index Create Memory setting
As a best practice it is highly recommended to set the value of “Index Create Memory KB” to zero. This will help SQL Server to allocate memory for index creation dynamically.
In this page you can also find different settings related to SQL Server Instance Level memory settings like Minimum Server Memory (in MB), Maximum Server Memory (in MB), Index Creation Memory (in KB) and Minimum Memory per Query (in KB).
How to change Index Create Memory setting using TSQL
One can also change the value of Index Create Memory (KB) using the sp_configure system stored procedure. Execute the below mentioned TSQL script to set the value as zero for Index Create Memory (KB) so that SQL Server can dynamically manage the memory.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Use master GO EXEC sys.sp_configure 'show advanced options', 1; GO RECONFIGURE GO EXEC sys.sp_configure 'index create memory (KB)', N'0' GO RECONFIGURE GO EXEC sys.sp_configure 'show advanced options', 0 GO RECONFIGURE GO |
If you are an experienced database administrator then you can set the value for Index Create Memory (KB) as 2048 considering in this example Max Degree of Parallelism value is set to 2. However, if someone makes changes to Max Degree of Parallelism then this issue could repeat again. Hence, it is highly recommended to set the value of “Index Create Memory KB” as zero to avoid this issue in future.
Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft and Cognizant.
He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.
View all posts by Ashish Kumar Mehta