配置 index create memory 服务器配置选项Configure the index create memory Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何使用 SQL ServerSQL Server 中配置 SQL Server Management StudioSQL Server Management Studio index create memory Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the index create memory server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 在创建索引时,index create memory 选项控制最初为排序操作分配的最大内存量。The index create memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. 此选项的默认值为 0(自动配置)。The default value for this option is 0 (self-configuring). 如果随后创建索引时需要更多内存,而且有内存可供使用,服务器将使用可用的内存,从而超出此选项的设置。If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. 如果没有内存可供使用,则继续使用已分配的内存来创建索引。If additional memory is not available, the index creation will continue using the memory already allocated.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • min memory per query 选项的设置优先于 index create memory 选项的设置 。The setting of the min memory per query option has precedence over the index create memory option. 如果更改这些选项,使 index create memory 小于 min memory per query,则会收到警告消息,但设置的值仍会生效。If you change both options and the index create memory is less than min memory per query, you receive a warning message, but the value is set. 而且,在查询执行过程中,您还会收到类似的警告。During query execution, you receive a similar warning.

  • 使用已分区表和已分区索引时,如果出现非对齐的已分区索引且并行度很高,则创建索引时的最低内存要求将显著提高。When using partitioned tables and indexes, the minimum memory requirements for index creation may increase significantly if there are non-aligned partitioned indexes and a high degree of parallelism. 此选项控制在单一索引创建操作中为所有索引分区分配的初始内存总量。This option controls the total initial amount of memory allocated for all index partitions in a single index creation operation. 如果此选项设置的内存量小于运行查询所需的最小内存,查询将终止并显示错误消息。The query will terminate with an error message if the amount set by this option is less than the minimum required to run the query.

  • 此选项的运行值不会超过用于运行 SQL ServerSQL Server 的操作系统和硬件平台的实际内存量。The run value for this option will not exceed the actual amount of memory that can be used for the operating system and hardware platform on which SQL ServerSQL Server is running.

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • index create memory 选项是自行配置的,通常不需要调整即可工作。The index create memory option is self-configuring and usually works without requiring adjustment. 但如果在创建索引时遇到困难,可以考虑提高此选项的运行值。However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.

  • 在生产系统上创建索引通常是不常执行的任务,通常安排在非高峰时段执行。Creating an index on a production system is usually an infrequently performed task, often scheduled as a job to execute during off-peak time. 因此,不常创建索引且在非峰值时间时,增加 index create memory 可提高索引创建的性能 。Therefore, when creating indexes infrequently and during off-peak time, increasing the index create memory can improve the performance of index creation. 但是,最好将 min memory per query 配置选项保持在一个较低的值,这样即使所有请求的内存都不可用,索引创建作业仍能开始 。Keep the min memory per query configuration option at a lower number, however, so the index creation job still starts even if all the requested memory is not available.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置 index create memory 选项To configure the index create memory option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “内存” 节点。Click the Memory node.

  3. “创建索引占用的内存” 下,为 index create memory 选项键入或选择所需的值。Under Index creation memory, type or select the desired value for the index create memory option.

    index create memory 选项用于控制索引创建排序时所需的内存量。Use the index create memory option to control the amount of memory used by index creation sorts. index create memory 选项是自配置选项,在大多数情况下不需调整即可正常工作。The index create memory option is self-configuring and should work in most cases without requiring adjustment. 但如果在创建索引时遇到困难,可以考虑提高此选项的运行值。However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value. 查询排序由 min memory per query 选项控制。Query sorts are controlled through the min memory per query option.

使用 Transact-SQLUsing Transact-SQL

配置 index create memory 选项To configure the index create memory option

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_configureindex create memory 选项的值设置为 4096This example shows how to use sp_configure to set the value of the index create memory option to 4096.

USE AdventureWorks2012 ;  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'index create memory', 4096  
GO  
RECONFIGURE;  
GO  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置 index create memory 选项之后Follow Up: After you configure the index create memory option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

sys.configurations (Transact-SQL) sys.configurations (Transact-SQL)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
“服务器内存”服务器配置选项 Server Memory Server Configuration Options
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)