SQLServer重建索引RebuildIndex

重建索引是為了減少數據碎片。數據碎片會導致 SQL Server 進行不必要的數據讀，降低 SQL Server 的性能。重建索引也會同時更新列統計，而如果查詢所使用的列缺少或遺漏統計信息，這可能導致 SQL Server 內部的優化器選擇比預期效率低的查詢計劃。 如果您重建

重建索引是為了減少數據碎片。數據碎片會導致SQL Server進行不必要的數據讀，降低SQL Server的性能。重建索引也會同時更新列統計，而如果查詢所使用的列缺少或遺漏統計信息，這可能導致SQL Server內部的優化器選擇比預期效率低的查詢計劃。

如果您重建了某張表上的聚集索引，該表上的非聚集索引也同時會被更新。

要更新索引，您可以使用Maintenance Wizard（相關內容您可以參考http://msdn.microsoft.com/en-us/library/ms180074.aspx），或在SQL Server代理（Agent）中運行如下的自定義代碼來更新某個數據庫中所有表上的索引：

您可以根據您的需求修改DBREINDEX的參數。

需要注意的是，重建非聚集索引時該表會暫時加上共享鎖，對用戶不可進行SELECT以外的操作；重建聚集索引時該表會暫時加上排外鎖，不允許任何用戶訪問。因此需要制定好計劃來預防可能的訪問問題。

REBUILD有一個fill factor參數，如果fill factor設置為100%，這意味著每一個索引頁都是完全滿的，如果fill factor設置為50%意味著每個索引頁都是半滿的。對于fill factor 100%，每次新插入或更新一個記錄，由于當前頁沒有空間可用，可能有分頁情況產生。過多的分頁會降低SQL Server的性能。下面具體舉個例子：

假設您在一張表上建立了一個使用默認fill factor的新索引。當SQL Server創建索引時，它會把索引放置在連續的物理頁上，以使數據順序地被讀，I/O訪問最優化。但當表因INSERT，UPDATE，DELETE等操作增長改變時，分頁發生，SQL Server在磁盤的其他地方分配新的頁，導致新的頁與原物理頁不連續，增加了隨機I/O，訪問索引頁變慢。

那么fill factor的合適值應該為多少？這取決于表的讀/寫比：

低更新表（讀/寫比：100比1）：100% fill factor

高更新表（寫超過讀）：50%-70% fill factor

居中：80%-90% fill factor

過低的fill factor會增加頁的數量，也會導致更多的頁需要被移至緩存，緩存中有用的數據減少。默認的fill factor為0（即100% fill factor），通常這不是個好的選擇，特別是對于聚集索引。

如果您無法判斷設置什么fill factor,您首先需要確定磁盤的讀/寫比.方法就是使用如下兩個計數器:

Physical Disk Object: % Disk Read Time 和 Physical Disk Object: % Write Time。另外一個可能有用的計數器就是：SQL Server Access Methods: Pages Splits/Sec。這個計數器測量SQL Server內每秒分頁的次數。如果該數值過高，您需要降低fill factor防止新的分頁。

如果您想確認您的索引因分頁產生的碎片程度，您可以運行DBCC SHOWCONTIG命令。如果看特定表和特定索引，您可以運行如下代碼：

結果集中最重要的參數是Scan Density，越接近100%越好。如果Scan Density小于75%，那么您可能需要重建表中的索引。

對于小于100數據頁，重建索引并不會有明顯的性能改善。這是因為物理硬件緩存，SQL Server緩存和SQL Server預讀機制隱藏了碎片的負面作用。但對于非常大的表，重建索引會使它受益匪淺，因為涉及大量磁盤I/O操作。

ALTER INDEX Idx_name ON tblCompany REBUILD

 USE databasename --Enter the name of the database you want to reindex
DECLARE @TableName VARCHAR(255)
DECLARE tablecursor CURSOR FOR
 SELECT table_name
 FROM information_schema.tables
 WHERE table_type = 'base table'

OPEN tablecursor

FETCH next FROM tablecursor INTO @TableName

WHILE @@FETCH_STATUS = 0
 BEGIN
 DBCC dbreindex(@TableName, '', 90)

 FETCH next FROM tablecursor INTO @TableName
 END

CLOSE tablecursor

DEALLOCATE tablecursor 

 --Script to identify table fragmentation
--Declare variables
DECLARE @ID INT,
 @IndexID INT,
 @IndexName VARCHAR(128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = Object_id('table_name') --enter name of table
--Get the Index Values
SELECT @IndexID = indid
FROM sysindexes
WHERE id = @ID
 AND name = @IndexName

--Display the fragmentation
DBCC showcontig (@id, @IndexID) 

--方法一：

 USE adventureworks

go

EXEC Sp_msforeachtable
 @command1="print '?' DBCC DBREINDEX ('?', '', 90)"

go

EXEC Sp_updatestats

go 

--方法二：

USE adventureworks

go

CREATE PROCEDURE Usp_reindex_updatestats
AS
 DECLARE @MyTable VARCHAR(255)
 DECLARE mycursor CURSOR FOR
 SELECT table_name
 FROM information_schema.tables
 WHERE table_type = 'base table'
 AND table_name NOT LIKE 'sys%'

 OPEN mycursor

 FETCH next FROM mycursor INTO @MyTable

 WHILE @@FETCH_STATUS = 0
 BEGIN
 PRINT 'Reindexing Table: ' + @MyTable

 DBCC dbreindex(@MyTable, '', 90)

 FETCH next FROM mycursor INTO @MyTable
 END

 CLOSE mycursor

 DEALLOCATE mycursor

 EXEC Sp_updatestats

go 

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com