怎樣獲得SQL Server索引使用方式

原文出自：

http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

轉載出自：http://blog.csdn.net/dba_huangzj/article/details/7777392

問題：

資料庫有兩大儲存需要：資料頁和索引頁。在你的表中理解和查看實際的資料,是相當簡單的。通過運行一些查詢樣本來瞭解哪些列被使用，以及什麼類型的資料實際上是被儲存。一方面，很難知道索引實際上是如何被使用和怎麼使用的。所以你應該如何更好地理解索引的使用及正在發生什麼操作（inserts,updates,deletes,selects）？

解決方案：

在SQLServer中，許多新的DMVs被引入，供你窺探一些過往版本中不能或很難看到的資料。其中一個新函數和一個新視圖是提供索引使用方式：

Sys.dm_db_index_operational_stats和sys.dm_db_index_usage_stats

Sys.dm_db_index_operational_stats：

該函數提供關於insert/update/delete在特定索引上引起的資訊。另外，這個視圖同樣提供關於鎖、閂鎖和存取方法的資訊。它會返回很多列，但是重點關注下面幾列：

• leaf_insert_count –葉子層級插入的總數
• leaf_delete_count -葉子層級刪除的總數
• leaf_update_count  -葉子層級更新的總數

以下例子的查詢提供一些關於增、刪、改操作的有用資訊的關鍵列：

SELECT
OBJECT_NAME(A.[OBJECT_ID])
AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS
(NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES
AS I
         ON I.[OBJECT_ID]
= A.[OBJECT_ID]
            AND I.INDEX_ID
= A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable')
= 1

以下是輸出情況，從這個函數中，我們可以得知有多少insert/update/delete操作發生在每個表和索引上。

Sys.dm_db_index_usage_stats：

這個視圖提供關於你的索引的存取方法總覽情況，下面的列在檢查問題是比較有用：

• user_seeks – 索引尋找的次數。
• user_scans- 索引掃描的次數。
• user_lookups – 書籤尋找的次數。
• user_updates – 增刪改操作的次數。

SELECT  
OBJECT_NAME(S.[OBJECT_ID])
AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS
AS S
         INNER JOIN SYS.INDEXES
AS I
           ON I.[OBJECT_ID]
= S.[OBJECT_ID]
              AND I.INDEX_ID
= S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable')
= 1

以下是查詢結果，從這個結果中可以得知有多少尋找、掃描、書籤尋找和全部修改操作。

注意：

當SQLServer重啟時這些值會清空，另外，sys.dm_db_index_operational_stats返回的值僅僅是只對緩衝在堆（heap）或者索引的中繼資料對象有效。