SQL Server
診斷索引健全狀況的新工具
Randy Dyess
摘要:
- 索引片段
- 索引使用方式
- 索引作業活動
下載本文程式碼: DyessSQLIndex2007_03.exe (151KB)
所有可最佳化 SQL Server 效能的項目當中，最主要的項目之一是要在資料庫內微調索引。SQL Server 查詢最佳化工具在執行查詢期間是否可正確使用索引，除了端賴建立有效的索引之外，
還必須仰賴索引的健全狀況。SQL Server™ 2005 推出的一系列動態管理檢視 (DMV) 和動態管理函數 (DMF)，有助於資料庫管理員判斷索引的效能，並找出所有效能問題。
DMV 和 DMF 可供您深入探究伺服器，並利用伺服器狀態傳回的資訊，幫助您監視伺服器執行個體的健全狀況與效能，同時診斷問題。熟悉舊版 SQL Server 的資料庫管理員不難發現，這些 DMV 和 DMF 可用於取代 DBCC 命令、執行某些系統預存程序、查詢諸多系統資料表，以及使用 SQL Profiler 擷取事件。
三個主要的函數與檢視分別為 sys.dm_db_index_physical_stats、sys.dm_db_index_usage_stats 及 sys.dm_db_index_operational_stats，可用來協助您了解索引是否根據預期的方式運作。這些函數與檢視可供您檢閱索引的 I/O 與鎖定模式，同時可了解查詢最佳化工具運用索引的方式，是否會造成資料庫中不必要的問題。
索引片段
建立 sys.dm_db_index_physical_stats DMF 是為了取代 DBCC SHOWCONTIG，並且會顯示出索引的片段。然而不同的是，DBCC SHOWCONTIG 會將共用鎖定 (S) 置於包含索引的資料表中，而 sys.dm_db_index_physical_stats 只會放置目標共用鎖定 (IS)，大幅減少函數執行時的資料表封鎖情形。
若要使用 sys.dm_db_index_physical_stats 來判斷索引片段，您必須在函數的輸出中，檢查三個資料行的組合。索引邏輯片段 (堆積範圍片段) 可藉由檢查 avg_fragmentation_in_percent 資料行傳回值來判斷。邏輯片段是指索引分葉層級中頁面次序不對的百分比，而範圍片段則是指索引分葉層級中範圍次序不對的百分比。 邏輯和範圍片段可能會導致更頻繁的 I/O 和磁碟讀寫頭的移動，因而影響索引效能，因為磁碟讀寫頭必須跳來跳去，才能按照順序讀取頁面資料。您應該努力維持邏輯和範圍的片段都盡量趨近於零。
索引的內部片段是頁面飽和度的百分比。你當然希望索引頁面愈飽和愈好，但是又需要在飽和度與索引頁面插入數量之間取得平衡，才能維持最低的頁面分割數目。
sys.dm_db_index_physical_stats 的 avg_page_space_used_in_percent 引數，需要經過檢查以判斷索引頁面的飽和度。若要正確設定此數字與百分之百的差距，請調整索引的填滿因數，同時監看出現的頁面分割數目。到了臨界點，頁面分割的數目會急遽增加，這表示索引的填滿因數設定過高。調整索引的填滿因數很花時間，而且還需要測試，因此請不要在未經確實計劃之前就採取行動 (沒有隨機插入的索引可以將其填滿因數設定為 100，也不必擔心頁面分割數目會增加)。
若要判斷 AdventureWorks.HumanResources.Employee 資料表中所有索引的片段程度，您可以使用如下的陳述式：
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL
-- NULL to view all indexes;
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information
利用此 DMF 即可自動判斷出哪些索引需要重新建立、哪些需要重新組織，以及哪些不需要維護。檢查此 DMF 中 avg_page_space_used_in_percent 和 avg_fragmentation_in_percent 資料行的值，即可找出落在可接受邏輯與密度臨界值以外的索引片段，這有助於您判斷需要在索引上執行哪一種作業。
視您的索引狀態而定，[圖 1] 中顯示的範例可能不會傳回 AdventureWorks 範例資料庫副本中的資料，但是這可以進行調整，以運用在其他資料庫上。
Figure 1 檢查頁面飽和度與片段程度
--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO
SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO
SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
比較簡單的方法，是將查詢結果儲存到資料表變數中，接著在資料表變數中進行迴圈，以便為正確的 ALTER INDEX 陳述式建立動態字串 (請參閱 [圖 2])。
Figure 2 建立動態 ALTER INDEX 字串
--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO
--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)
INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD'
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id
索引使用方式
雖然 sys.dm_db_index_physical_stats 的功能強大且可以取代 DBCC SHOWCONTIG，同時有助於顯示出索引的健全狀況，但您仍然經常必須面對更複雜的問題，亦即需要判斷哪些索引適用於針對資料表所執行的查詢。通常資料庫開發人員或管理員會在資料表中，建立他們認為查詢最佳化工具會在執行查詢時使用的索引。要在舊版的 SQL Server 中得知是否實際使用這些索引，可說是難上加難。因為若不是得卸除索引，看看查詢效能是否受到影響，否則就是要擷取查詢的執行計劃，掃描索引的使用方式。
然而全新的動態管理檢視 sys.dm_db_index_usage_stats，可以讓您輕鬆了解在查詢最佳化工具以及針對資料表執行的查詢中，索引的實際使用情形。檢查此檢視即可判斷出索引的效益，讓您卸除任何查詢最佳化工具未使用的索引。您不再需要擔心索引是否只是在浪費儲存空間，也不必憂慮未使用的索引維護工作是否降低了資料庫效能。
只要以搜尋和掃描零的方式檢查此索引的 DMV 輸出，就能判斷出某索引在上次啟動 SQL Server 之後，是否曾經使用過。不過請記住，有許多 DMV 和 DMF 無法保存，每次重新啟動 SQL Server 就會歸零。在使用 DMV 或 DMF 來判斷索引使用方式的時候，請將這一點納入考量。有可能只是從上次重新啟動服務之後，還不需要使用該索引，但是到了週末、月底或每季報告查詢時，可能就需要用到該索引。
若要檢視自從上次重新啟動 SQL Server 服務之後，執行個體中尚未使用的所有索引，您可以利用下列陳述式：
SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0
索引作業活動
如果您想了解索引的作業活動，sys.dm_db_index_operational_stats DMF 就能派上用場。您可利用它來檢視資料庫中每個索引的 I/O、鎖定、閂鎖和存取方法活動，以幫助您了解索引的使用方式，並診斷是否有大量 I/O 活動或「熱門」索引的存在，而造成索引鎖定的問題。
使用此 DMF 的閂鎖等候資料行，即可協助建立讀取和寫入作業在取得索引資源存取權時，所花費的時間量。這可幫助您判斷用來儲存索引的磁碟子系統，是否足以負荷該索引的 I/O 活動。此外，這還能指出索引的設計與使用，是否會造成索引過於「熱門」，使得索引中一頁或多頁的大量活動，造成這些頁面資料的存取產生瓶頸。這種問題通常會導致過度封鎖，而無法讀取或寫入此區域。
[圖 3] 顯示如何判斷 AdventureWorks.HumanResources.Employee 資料表中，所有索引的鎖定與 I/O 模式。
Figure 3 判斷鎖定與 I/O 模式
SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)
了解其他資訊
本文中提及的 DMV 和 DMF 除了上述功能之外，還有許多其他用途。請參考 [其他資源] 資訊看板中所提及的 SQL Server 線上叢書，包括說明這些函數和檢視的文章，以便了解各種可用來傳回和檢查的資訊。
若要學習本文中未探討的其他索引 DMF 和 DMV，請參閱 SQL Server 查詢最佳化小組的部落格文章，網址為 blogs.msdn.com/queryoptteam/570176.aspx。
其他資源
Randy Dyess 是 Solid Quality Learning 的顧問，專精於 SQL Server OLTP 系統。Randy 也是眾多 SQL Server 相關書籍和文章的作者。他是 www.TransactSQL.Com 和 www.Database-Security.Info 網站的發起人和主要作者。
© 2008 Microsoft Corporation and CMP Media, LLC. 保留所有權利；未經允許，嚴禁部分或全部複製.