標簽：SQL SERVER/MSSQL SERVER/數據庫/DBA/索引/統計信息

無論何時對基礎數據執行插入、更新或刪除操作，SQL Server 數據庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導致索引中的信息分散在數據庫中（含有碎片）。當索引包含的頁中的邏輯排序（基于鍵值）與數據文件中的物理排序不匹配時，就存在碎片。碎片非常多的索引可能會降低查詢性能，導致應用程序響應緩慢，所以在日常的維護工作當中就需要對索引進行檢查對那些填充度很低碎片量大的索引進行重新生成或重新組織，但是在這個過程也需要注意一些小的細節，否則會產生錯誤。

語法內容載自SQL Server聯機叢書，標記出了需要注意的內容，最后分享自己平時用的維護索引的語句供參考。

ALTER INDEX { index_name | ALL }    ON <object>    { REBUILD         [ [PARTITION = ALL]          [ WITH ( <rebuild_index_option> [ ,...n ] ) ]           | [ PARTITION = partition_number                 [ WITH ( <single_partition_rebuild_index_option>                        [ ,...n ] )                ]             ]        ]    | DISABLE    | REORGANIZE         [ PARTITION = partition_number ]        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]  | SET ( <set_index_option> [ ,...n ] )     }[ ; ]<object> ::={    [ database_name. [ schema_name ] . | schema_name. ]        table_or_view_name}<rebuild_index_option > ::={    PAD_INDEX = { ON | OFF }  | FILLFACTOR = fillfactor   | SORT_IN_TEMPDB = { ON | OFF }  | IGNORE_DUP_KEY = { ON | OFF }  | STATISTICS_NORECOMPUTE = { ON | OFF }  | ONLINE = { ON | OFF }   | ALLOW_ROW_LOCKS = { ON | OFF }  | ALLOW_PAGE_LOCKS = { ON | OFF }  | MAXDOP = max_degree_of_parallelism  | DATA_COMPRESSION = { NONE | ROW | PAGE }      [ ON PARTITIONS ( { <partition_number_expression> | <range> }      [ , ...n ] ) ]}<range> ::= <partition_number_expression> TO <partition_number_expression>}<single_partition_rebuild_index_option> ::={    SORT_IN_TEMPDB = { ON | OFF }  | MAXDOP = max_degree_of_parallelism  | DATA_COMPRESSION = { NONE | ROW | PAGE } }}<set_index_option>::={    ALLOW_ROW_LOCKS = { ON | OFF }  | ALLOW_PAGE_LOCKS = { ON | OFF }  | IGNORE_DUP_KEY = { ON | OFF }  | STATISTICS_NORECOMPUTE = { ON | OFF }}

index_name

索引的名稱。索引名稱在表或視圖中必須唯一，但在數據庫中不必唯一。索引名稱必須符合標識符的規則。

ALL

指定與表或視圖相關聯的所有索引，而不考慮是什么索引類型。如果有一個或多個索引脫機或不允許對一個或多個索引類型執行只讀文件組操作或指定操作，則指定 ALL 將導致語句失敗。下表列出了索引操作和不允許使用的索引類型。

已分區表和已分區索引。

database_name

數據庫的名稱。

schema_name

表或視圖所屬架構的名稱。

table_or_view_name

與該索引關聯的表或視圖的名稱。若要顯示對象的索引報表，請使用 sys.indexes 目錄視圖。

REBUILD [ WITH (<rebuild_index_option> [ ,...n]) ]

指定將使用相同的列、索引類型、唯一性屬性和排序順序重新生成索引。此子句等同于 DBCC DBREINDEX。REBUILD 啟用已禁用的索引。重新生成聚集索引并不重新生成關聯的非聚集索引，除非指定了關鍵字 ALL。如果未指定索引選項，則應用存儲在 sys.indexes 中的現有索引選項值。對于未在 sys.indexes 中存儲值的任何索引選項，應用該選項的參數定義中指示的默認值。

重新生成 xml 索引或空間索引時，選項 ONLINE = ON 和 IGNORE_DUP_KEY = ON 無效。

如果指定 ALL 且基礎表為堆，則重新生成操作對表沒有任何影響。重新生成與表相關聯的所有非聚集索引。

如果數據庫恢復模式設置為大容量日志記錄或簡單，則可以對重新生成操作進行最小日志記錄。

PARTITION

指定只重新生成或重新組織索引的一個分區。如果 index_name 不是已分區索引，則不能指定 PARTITION。

PARTITION = ALL 重新生成所有分區。當指定PARTITION = ALL時不能使用ONLINE = ON

partition_number

要重新生成或重新組織已分區索引的分區數。partition_number 是可以引用變量的常量表達式。其中包括用戶定義類型變量或函數以及用戶定義函數，但不能引用 Transact-SQL 語句。partition_number 必須存在，否則，該語句將失敗。

WITH (<single_partition_rebuild_index_option>)

SORT_IN_TEMPDB、MAXDOP 和 DATA_COMPRESSION 是在重新生成單個分區 (PARTITION = n) 時可以指定的選項。不能在單個分區重新生成操作中指定 XML 索引。

不能聯機重新生成分區索引。在此操作過程中將鎖定整個表。

DISABLE

將索引標記為已禁用，從而不能由 數據庫引擎使用。任何索引均可被禁用。已禁用的索引的索引定義保留在沒有基礎索引數據的系統目錄中。禁用聚集索引將阻止用戶訪問基礎表數據。若要啟用索引，請使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。

REORGANIZE

指定將重新組織的索引葉級。此子句等同于 DBCC INDEXDEFRAG。ALTER INDEX REORGANIZE 語句始終聯機執行。這意味著不保留長期阻塞的表鎖，且對基礎表的查詢或更新可以在 ALTER INDEX REORGANIZE 事務處理期間繼續。不能為已禁用的索引或 ALLOW_PAGE_LOCKS 設置為 OFF 的索引指定 REORGANIZE。

WITH ( LOB_COMPACTION = { ON | OFF } )

指定壓縮所有包含大型對象 (LOB) 數據的頁。LOB 數據類型包括 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。壓縮此數據可以改善磁盤空間使用情況。默認值為 ON。

ON

壓縮所有包含大型對象數據的頁。

重新組織指定的聚集索引將壓縮聚集索引中包含的所有 LOB 列。重新組織非聚集索引將壓縮作為索引中非鍵（已包括）列的所有 LOB 列。有關詳細信息，請參閱創建帶有包含列的索引。

指定 ALL 時，將重新組織與指定表或視圖相關聯的所有索引，并且壓縮與聚集索引、基礎表或具有包含列的非聚集索引相關聯的所有 LOB 列。

OFF

不壓縮包含大型對象數據的頁。

OFF 對堆沒有影響。

如果 LOB 列不存在，則忽略 LOB_COMPACTION 子句。

SET ( <set_index option> [ ,...n] )

指定不重新生成或重新組織索引的索引選項。不能為已禁用的索引指定 SET。

PAD_INDEX = { ON | OFF }

指定索引填充。默認值為 OFF。

ON

FILLFACTOR 指定的可用空間百分比應用于索引的中間級頁。如果在 PAD_INDEX 設置為 ON 的同時不指定 FILLFACTOR，則使用 sys.indexes 中存儲的填充因子值。

OFF 或不指定 fillfactor

中間級頁已填充到接近容量限制。這樣將至少為索引可以基于中間頁中的鍵集擁有的最大大小的一行留出足夠的空間。

FILLFACTOR = fillfactor

指定一個百分比，指示在創建或更改索引期間，數據庫引擎對各索引頁的葉級填充的程度。fillfactor 必須為介于 1 至 100 之間的整數值。默認值為 0。

填充因子的值 0 和 100 在所有方面都是相同的。

顯式的 FILLFACTOR 設置只是在索引首次創建或重新生成時應用。數據庫引擎并不會在頁中動態保持指定的可用空間百分比。有關詳細信息，請參閱 CREATE INDEX (Transact-SQL)。

若要查看填充因子設置，請使用 sys.indexes。

使用 FILLFACTOR 值創建或更改聚集索引會影響數據占用的存儲空間量，因為數據庫引擎在創建聚集索引時會再分發數據。

SORT_IN_TEMPDB = { ON | OFF }

指定是否在 tempdb 中存儲排序結果。默認值為 OFF。

ON

在 tempdb 中存儲用于生成索引的中間排序結果。如果 tempdb 位于不同于用戶數據庫的磁盤集中，這樣可能會縮短創建索引所需的時間。但是，這會增加索引生成期間所使用的磁盤空間量。

OFF

中間排序結果與索引存儲在同一數據庫中。

如果不需要執行排序操作，或者可以在內存中進行排序，則忽略 SORT_IN_TEMPDB 選項。

IGNORE_DUP_KEY = { ON | OFF }

指定在插入操作嘗試向唯一索引插入重復鍵值時的錯誤響應。IGNORE_DUP_KEY 選項僅適用于創建或重新生成索引后發生的插入操作。當執行 CREATE INDEX、ALTER INDEX 或 UPDATE 時，該選項無效。默認值為 OFF。

ON

向唯一索引插入重復鍵值時將出現警告消息。只有違反唯一性約束的行才會失敗。

OFF

向唯一索引插入重復鍵值時將出現錯誤消息。整個 INSERT 操作將被回滾。

對于對視圖創建的索引、非唯一索引、XML 索引、空間索引以及篩選的索引，IGNORE_DUP_KEY 不能設置為 ON。

若要查看 IGNORE_DUP_KEY，請使用 sys.indexes。

在向后兼容的語法中，WITH IGNORE_DUP_KEY 等效于 WITH IGNORE_DUP_KEY = ON。

STATISTICS_NORECOMPUTE = { ON | OFF }

指定是否重新計算分發統計信息。默認值為 OFF。

ON

不會自動重新計算過時的統計信息。

OFF

啟用統計信息自動更新功能。

若要恢復統計信息自動更新，請將 STATISTICS_NORECOMPUTE 設置為 OFF，或執行 UPDATE STATISTICS 但不包含 NORECOMPUTE 子句。

如果禁用分發統計信息的自動重新計算，可能會阻止查詢優化器為涉及該表的查詢挑選最佳執行計劃。

ONLINE = { ON | OFF }

指定在索引操作期間基礎表和關聯的索引是否可用于查詢和數據修改操作。默認值為 OFF。

對于 XML 索引或空間索引，僅支持 ONLINE = OFF。如果 ONLINE 設置為 ON，則會引發錯誤。

聯機索引操作僅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

ON

在索引操作期間不持有長期表鎖。在索引操作的主要階段，源表上只使用意向共享 (IS) 鎖。這樣，即可繼續對基礎表和索引進行查詢或更新。操作開始時，將對源對象保持極短時間的共享 (S) 鎖。操作結束時，如果創建非聚集索引，將對源持有極短時間的 S 鎖；當聯機創建或刪除聚集索引時，或者重新生成聚集或非聚集索引時，將獲取 SCH-M（架構修改）鎖。對本地臨時表創建索引時，ONLINE 不能設置為 ON。

OFF

在索引操作期間應用表鎖。創建、重新生成或刪除聚集索引、空間索引或 XML 索引或者重新生成或刪除非聚集索引的脫機索引操作將獲得對表的架構修改 (Sch-M) 鎖。這樣可以防止所有用戶在操作期間訪問基礎表。創建非聚集索引的脫機索引操作將對表獲取共享 (S) 鎖。這樣可以防止更新基礎表，但允許讀操作（如 SELECT 語句）。

有關詳細信息，請參閱聯機索引操作的工作方式。有關鎖的詳細信息，請參閱鎖模式。

索引（包括全局臨時表中的索引）可以聯機重新生成，但以下索引除外：

• 禁用的索引
• XML 索引
• 本地臨時表中的索引
• 分區索引
• 聚集索引（如果基礎表包含 LOB 數據類型）。
• 使用 LOB 數據類型列定義的非聚集索引

如果表包含 LOB 數據類型，但這些列中沒有任何列在索引定義中用作鍵列或非鍵列，則可以聯機重新生成非聚集索引。

ALLOW_ROW_LOCKS