設定 fill factor 伺服器組態選項Configure the fill factor Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此主題描述如何使用 ,在 SQL Server 2017SQL Server 2017 中設定 SQL Server Management StudioSQL Server Management Studio fill factor Transact-SQLTransact-SQL伺服器組態選項。This topic describes how to configure the fill factor server configuration option in SQL Server 2017SQL Server 2017 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.

安全性Security

權限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. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

設定 fill factor 選項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

設定 fill factor 選項To configure the fill factor option

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase 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)