在SQL Server中配置索引创建内存设置的最佳实践

介绍 (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.

索引创建内存设置是高级SQL Server配置设置，SQL Server数据库引擎使用该设置来控制可为创建索引分配的最大内存量。 在本文中，我们将介绍解决以下提到的错误消息的步骤。

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.

如果“ 索引创建内存（KB） ”设置的配置值低于创建索引所需的内存量，则可能会开始看到此消息。

什么是索引创建内存设置，为什么它很重要？ (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.

在SQL Server中，将执行权限（默认情况下）授予sp_configure系统存储过程，不带任何参数，或者仅向所有用户提供初始参数。 但是，必须授予用户ALTER SETTINGS服务器级别的权限才能执行sp_configure ，以便能够将现有配置值更改为新的值并执行RECONFIGURE语句。

The ALTER SETTINGS permission is available only to the members of SERVERADMIN or SYSADMIN fixed server roles.

ALTER SETTINGS权限仅对SERVERADMIN或SYSADMIN固定服务器角色的成员可用。

索引创建内存设置的主要限制和限制 (Key limitations and restrictions for Index Create Memory settings )

1. 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.

   在更改“ 索引创建内存”高级设置时，您需要始终确保值“ 索引创建内存（KB）”大于“ 每个查询的最小内存（KB）”设置，否则即使配置了该值，您仍会收到警告消息。

2. The value specified for Index Create Memory should never surpass the actual amount of memory which is available on the SQL Server.

   为“索引创建内存”指定的值绝不能超过SQL Server上可用的实际内存量。

3. 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).

   如果使用分区表和索引，则创建索引的最低内存要求可能会增加。 如果存在未对齐的分区索引，并且您还配置了高度并行度（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.

重要说明 ：在SQL Server中更改索引创建内存值后，设置将立即生效。 因此，不需要回收SQL Server数据库引擎服务。 但是，在使用sp_configure系统存储过程或使用“ 服务器属性”对高级设置进行更改时，必须始终非常小心，因为这些更改会影响SQL Server实例的整体性能。

建议的最佳做法，用于配置“索引创建内存”高级设置 (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.

索引创建内存设置是一种高级配置设置，可用于控制可为创建索引分配的最大内存量。 强烈建议不要更改“ 索引创建内存（KB） ”高级设置的默认值。 “ 索引创建内存”高级设置的默认值为ZERO，这表示SQL Server将根据要求动态管理内存。

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.

如果SQL Server需要额外的内存来创建索引； 如果该内存可供数据库引擎使用，则SQL Server将使用它。 但是，如果没有额外的可用内存，则在此类场景期间，索引创建将继续使用当前分配的内存。 如果您在使用当前设置时开始遇到困难，请考虑增加“ 索引创建内存（KB） ”高级设置的值。

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.

让我们逐步介绍使用SQL Server Management Studio或执行sp_configure系统存储过程来更改SQL Server中的索引创建内存设置的方法。 更改索引创建内存高级服务器设置后，无需重新启动SQL Server数据库引擎服务，因为此设置立即生效。

如何使用SSMS更改索引创建内存设置 (How to change the Index Create Memory setting using SSMS)

1. Open SQL Server Management Studio and then connect to SQL Server Instance.

   打开SQL Server Management Studio，然后连接到SQL Server实例。

2. In Object Explorer, right click the SQL Server Instance and click on Properties as shown in the snippet below.

   在“ 对象资源管理器”中 ，右键单击“ SQL Server实例” ，然后单击“ 属性” ，如下面的代码片段所示。

   

3. 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.

   在“ 服务器属性”窗口中，单击左侧窗格中“ 选择页面”选项下的“ 内存”节点，如下面的代码片段所示。 在这里，您可以看到索引创建内存的配置值（以KB为单位）为768。

   Error Message

   错误信息

   

4. 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.

   根据上述错误消息，我们可以将“ 索引创建内存”的值（以KB为单位）设置为2048，即1024 KB值的两倍。 原因是在此SQL Server实例上， 最大并行度 （MAXDOP）设置为2。

5. 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.

   作为最佳实践，强烈建议将“ Index Create Memory KB ”的值设置为零。 这将有助于SQL Server动态分配内存以创建索引。

   

6. 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).

   在此页面中，您还可以找到与SQL Server实例级内存设置相关的不同设置，例如最小服务器内存（MB） ， 最大服务器内存（MB） ， 索引创建内存（KB）和每个查询的最小内存（KB） 。

如何使用TSQL更改索引创建内存设置 (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.

也可以使用sp_configure系统存储过程来更改“ 索引创建内存（KB）”的值。 执行下面提到的TSQL脚本，以将索引创建内存（KB）的值设置为零 ，以便SQL Server可以动态管理内存。

 
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.

如果您是一位经验丰富的数据库管理员，则可以在本示例中考虑“最大并行度”值设置为2的情况下，将“索引创建内存（KB）”的值设置为2048。但是，如果有人对“最大并行度”进行了更改，则会出现此问题可以再重复一次。 因此，强烈建议将“ 索引创建内存KB ”的值设置为零，以免将来出现此问题。

翻译自: https://www.sqlshack.com/best-practices-configure-index-create-memory-setting-sql-server/