配置填充因子服务器配置选项Configure the fill factor 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 Server 2019SQL Server 2019 中配置 SQL Server Management StudioSQL Server Management Studio “填充因子” Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the fill factor server configuration option in SQL Server 2019SQL Server 2019 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 提供填充因子是为了优化索引数据存储和性能。Fill factor is provided for fine-tuning index data storage and performance. 当创建或重新生成索引时,填充因子的值可确定每个叶级页上要填充数据的空间百分比,以便保留一些剩余空间作为以后扩展索引的可用空间。When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the rest as free space for future growth. 有关详细信息,请参阅 为索引指定填充因子For more information, see Specify Fill Factor for an Index.

本主题内容In This Topic

开始之前Before You Begin

建议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.

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

配置填充因子选项To configure the fill factor option

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

  2. 单击 “数据库设置” 节点。Click the Database Settings node.

  3. “默认索引填充因子” 框中,键入或选择所需的索引填充因子。In the Default index fill factor box, type or select the index fill factor that you want.

使用 Transact-SQLUsing Transact-SQL

配置填充因子选项To configure the fill factor 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_configurefill factor 选项的值设置为 100This example shows how to use sp_configure to set the value of the fill factor option to 100.

Use AdventureWorks2012;  
GO  
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'fill factor', 100;  
GO  
RECONFIGURE;  
GO  

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

跟进:在配置填充因子选项之后Follow Up: After you configure the fill factor option

必须重新启动服务器,设置才会生效。The server must be restarted before the setting can take effect.

另请参阅See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
为索引指定填充因子 Specify Fill Factor for an Index
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
sys.indexes (Transact-SQL)sys.indexes (Transact-SQL)