運維腳本：索引統計

資料庫引擎是高度優化的閉環系統，基於執行計劃的反饋，查詢優化器在一定程度上自動優化現有的執行計劃。查詢優化的核心是索引優化，資料庫引擎通過計數器統計關於索引操作的數據，統計的信息包括：使用次數、物理存儲、底層操作的計數，以及缺失索引等，這些統計數據存儲在內存中，是資料庫引擎執行情況的真實反饋，高度概括了索引的執行情況，有意識地利用索引的統計信息，有針對性地優化現有的業務邏輯代碼，調整查詢的執行計劃，能夠提高資料庫的查詢性能。

一，統計索引的使用次數

在用戶成功提交查詢語句時，執行計劃中每一個單獨的索引操作（Seek，Scan，Lookup或Update）都會被統計到sys.dm_db_index_usage_stats中，例如，user_updates計數器統計索引執行Insert,Update或Delete操作的次數，查找計數器（user_seeks, user_scans, user_lookups）統計在索引上執行的seek，scan和lookup操作的次數，如果查找計數器遠遠小於user_updates計數器，這說明基礎表會執行大量的更新操作，維護索引更新的開銷比較大，資料庫引擎利用索引提升查詢性能的空間有限。

在計數時，每一個單獨的seek、scan、lookup或update操作都被計算為對該索引的一次使用，並使該視圖中的相應計數器加1。

索引的Seek，Scan，Lookup和Update的含義是：

• Seek

  是Index Seek：通過該索引進行查找的次數

• Scan

  是Index Scan：通過該索引執行掃描查找的次數

• Lookup

  是Key Lookup：通過該索引查找到數據後，再到源數據表進行鍵值查找的次數，Key Lookup是非聚集索引特有的，查詢性能低下，應避免這種查找方法；

• Update

  是Index Update：由於源表數據更新導致索引頁更新的次數

Index Seek和Index Scan的區別是：

• Index Seek

  是從BTree的根節點開始，向子節點查找，直到葉子節點；

• Index Scan

  是在Index的葉子節點上，從左到右，把整個BTree的葉子節點遍歷一遍，類似於Table Scan。

如果索引的Seek，Scan，Lookup的計數值較多，那麼說明索引被引用的次數多；如果查找計數器數值較小，但是Update數值較多，說明維護Index的開銷高於查詢帶來的性能提升，應該考慮修改索引的結構，或者直接把索引刪除。

select db_name(us.database_id) as db_name
,object_schema_name(us.object_id)+"."+object_name(us.object_id) as table_name
,i.name as index_name
,i.type_desc as index_type_desc
,us.user_seeks
,us.user_scans
,us.user_lookups
,us.user_updates
from sys.dm_db_index_usage_stats us
inner join sys.indexes i
on us.object_id=i.object_id and us.index_id=i.index_id
where us.database_id=db_id
--us.database_id=db_id("database_name")
--and us.object_id=object_id("schema_name.table_name")
order by us.user_seeks desc

View Code

二，統計索引的物理存儲

使用 sys.dm_db_index_physical_stats 函數統計索引的物理存儲，例如，碎片的百分比，數據存儲的集中和分散程度，以及page空間的利用率等：

• avg_fragmentation_in_percent：索引外部碎片的百分比，值越大，說明索引的邏輯順序和物理順序差異越大，查找性能越低；
• fragment_count：分段的數量，表示索引數據的集中/分散程度；
• avg_fragment_size_in_pages：分段的大小
• avg_page_space_used_in_percent：索引內部碎片的百分比，值越大，說明page空間的利用率越高；

請閱讀《索引碎片的檢測和整理》，以了解更多。

三，底層操作的計數

使用 sys.dm_db_index_operational_stats函數統計底層IO、加鎖（Locking）、Latch和數據訪問模式的計數，通過這些數據，用戶能夠追蹤到查詢請求必須等待多長時間才能完成數據的讀寫、標識索引是否存在IO熱點。

在統計索引的底層操作之前，先了解跟數據的物理存儲相關的術語：

• 幽靈數據

  (ghost)是指：在索引的葉子節點中，數據行被標記為刪除，但是還沒有從索引結構中物理刪除，幽靈數據只存在於索引的葉子節點中，幽靈數據由後台進程定期執行物理刪除。

• 轉發數據

  （forwarding）：需要兩次IO操作才能獲取到指定的數據，轉發操作只發生於堆表（Heap）中；當數據行被更新，導致行的Size增大，以致於該行無法存儲在當前的page中，為了避免相關索引的更新，資料庫引擎會把該數據行轉存到一個新的Page中，並在新舊 Page中分別添加一個Pointer：在原Page中，Pointer指向新Page，該Pointer稱作Forwarder Pointer；在新page中，Pointer指向原Page，稱作Back Pointer。在讀取數據時，資料庫引擎首先從Forwarder Pointer中讀取數據存儲的指針，然後，根據指針到相應的地址空間中讀取真正的數據。

• 獲取

  （Fetch）數據：用於從LOB或Row_Overflow的分配單元（Allocation Unit）中取回（Retrive）數據，大欄位數據存儲在特定的LOB或Row_Overflow類型的數據頁中。

• 剝離

  （Push Off）數據列：用於統計資料庫引擎把LOB或Row-Overflow數據從原有的In-Row 數據頁剝離的次數。在執行Insert或Update操作之後，數據行的Size增長，不能存儲在當前的Page中，必須把大數據欄位的數據從原來的數據行中分離，存儲在指定的分配單元中，這個過程就是數據列的剝離。

• 拉回

  （Pull In）數據行：是Push Off的逆過程，用於統計資料庫引擎把數據從LOB或Row-Overflow數據頁拉入到In-Row數據頁的次數，拉入數據行一般發生在更新數據之後，數據行的Size減小，數據行在釋放存儲空間之後，能夠存儲在In-Row Page中，數據引擎把數據從LOB或Row-Overflow數據頁拉入到In-Row數據頁，這個過程是數據列的拉回。

This (pulled in-row) occurs when an update operation frees up space in a record and provides an opportunity to pull in one or more off-row values from the LOB_DATA or ROW_OVERFLOW_DATA allocation units to the IN_ROW_DATA allocation unit.

以下腳本用於統計索引底層的存儲動作和鎖/Latch的爭用：

select db_name(ops.database_id) as db_name
,object_schema_name(ops.object_id)+"."+object_name(ops.object_id) as table_name
,i.name as index_name
,ops.partition_number
,ops.leaf_insert_count
,ops.leaf_delete_count
,ops.leaf_update_count
,ops.leaf_ghost_count
,ops.nonleaf_insert_count
,ops.nonleaf_delete_count
,ops.nonleaf_update_count
,ops.range_scan_count
,ops.singleton_lookup_count
,ops.forwarded_fetch_count

,iif(ops.row_lock_wait_count=0,0,ops.row_lock_wait_in_ms/ops.row_lock_wait_count) as avg_row_lock_wait_ms
,iif(ops.page_lock_wait_count=0,0,ops.page_lock_wait_in_ms/ops.page_lock_wait_count) as avg_page_lock_wait_ms
,iif(ops.page_latch_wait_count=0,0,ops.page_latch_wait_in_ms/ops.page_latch_wait_count) as avg_page_latch_wait_ms
,iif(ops.page_io_latch_wait_count=0,0,ops.page_io_latch_wait_in_ms/ops.page_io_latch_wait_count) as avg_page_io_latch_wait_ms
from sys.dm_db_index_operational_stats(db_id,object_id("dbo.FactThread"),null,null) as ops
inner join sys.indexes i
on ops.object_id=i.object_id
and ops.index_id=i.index_id
order by index_name

View Code

該函數統計的Latch徵用數據主要分為PageLatch和PageIOLatch，其區別是：

• PageLatch

  是指：在訪問數據有關的數據頁（Data Page或Index Page）時，如果相應的Page已經存在於Buffer Pool中，那麼SQL Server先獲取buffer的latch，這個Latch就是 PageLatch，然後讀取Buffer中的數據。

  PageLatch是施加在Buffer上的Latch， 用來保護：Data page，Index Page, 系統page（PFS,GAM,SGAM,IAM等）的爭用訪問；在數據更新時，分配新的page，或拆分 索引頁（Index Page），會產生PageLatch 等待。

• PageIOLatch

  是指：用於把數據從索引或Heap中載入到內存。當數據頁從物理文件中的Page中讀取到內存時，申請對內存Buffer施加的Latch是PageIOLatch。當數據頁不在內存里時，SQL Server 先在內存中預留一個Page，然後從硬碟讀取，載入到內存Buffer中，此時，SQL Server申請並獲取的latch類型是PAGEIOLATCH，PageIOLatch表示正在進行IO操作。PageIOLatch_EX表示正在將disk中的數據頁載入到內存，PageIOLatch_SH表示在載入數據頁到內存期間，試圖讀取內存中的數據頁，此時載入數據頁的過程沒有完成，處於Loading狀態。如果經常出現PageIOLatch_SH，表明Loading數據頁的時間太長，可能出現IO bottleneck。

分析查詢結果，根據計數器的數值，調整資料庫，使系統達到最優狀態：

• 如果發現欄位leaf_ghost_count的數值特別大，說明索引中存儲很多幽靈數據，可以通過重建索引（Rebuild）清理幽靈數據行：

alter index index_name
on table_name
rebuild

• 如果PageIOLatch等待較多，說明資料庫頻繁的執行硬碟IO操作，可能的原因是內存不足，或者數據文件沒有分散到多個物理硬碟上
• 如果PageLatch等待較多，說明資料庫存在IO熱點，可以通過增加數據文件ndf，把資料庫分散到不同的物理硬碟上，以減少IO熱點

四，缺失索引

查詢優化器（Query Optimizer）在執行查詢時，如果檢測到執行計劃缺失索引，會把缺失索引的相關信息存儲在緩存中，通過 sys.dm_db_missing_index_details可以檢測查詢優化器建議創建的缺失索引。

該視圖返回的缺失索引的索引鍵及包含列信息，在索引列的順序上，相等列（equality）應該排在不等列（inequality）之前，包含列（Included）應該添加到INCLUDE子句中，但是，該視圖不會標識出相等列（equality）的排列順序，需要根據查詢語句和選擇性來設置，索引鍵的第一列至關重要。

select mid.index_handle
,db_name(mid.database_id) as db_name
,mid.object_id
,object_name(mid.object_id) as object_name
,mid.equality_columns
,mid.included_columns
,mid.included_columns
,mid.statement
,mic.column_id
,mic.column_name
,column_usage
from sys.dm_db_missing_index_details as mid
cross apply sys.dm_db_missing_index_columns(mid.index_handle) as mic
order by mid.object_id

View Code

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！