認識 sys.dm_db_index_usage_stats 目錄檢視，查詢與索引作業相關的計數器資料

sys.dm_db_index_usage_stats 目錄檢視
傳回不同類型索引作業的計數，以及每種類型作業上次執行的時間。

在指定索引上由一項查詢執行所進行的每個個別搜尋、掃描、查閱或更新，在這份檢視都被當作使用一次該索引， 並且累加對應的計數器。

它會針對由使用者提交之查詢所導致的作業，以及由內部產生之查詢所導致的作業 (例如，掃描以收集統計資料)，來報告資訊。

user_updates 計數器會指出索引上由基礎資料表或檢視的插入、更新或刪除作業所導致的維護層級。
您可以利用這份檢視，來判斷應用程式根本很少用到的索引。

您也可以利用這份檢視，來判斷哪些索引會產生維護負擔。您可能會考慮卸除產生維護負擔，但不用於查詢，或者不常用於查詢的索引。

只要一啟動 SQL Server (MSSQLSERVER) 服務，計數器就會初始化為空值。
此外，每當卸離或關閉資料庫時 (例如，因為 AUTO_CLOSE 設為 ON)，就會移除所有與資料庫關聯的資料列。

使用索引時，如果索引的資料列不存在，則會將資料列加入 sys.dm_db_index_usage_stats 中。
加入資料列後，其計數器的初始設定為零。

請參考以下的範例程式碼：

USE Northwind
GO

--01 檢視資料表 Orders 的索引類型
SELECT t.name N'資料表', i.name N'索引名稱', i.type_desc N'索引類型的描述'
FROM sys.tables t INNER JOIN sys.indexes i
ON t.object_id = i.object_id
WHERE t.name = 'Orders'

--02 叢集索引掃描(Clustered Index Scan)
SELECT * FROM Northwind.dbo.Orders

-- 觀察：last_user_seek、last_user_scan、last_user_lookup 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

--03 叢集索引搜尋(Clustered Index Seek)
SELECT * FROM Northwind.dbo.Orders
WHERE OrderID =10248

-- 觀察：last_user_seek、last_user_scan、last_user_lookup 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

--04 索引搜尋(NonClustered Index Seek) + 索引鍵查詢(Clustered Index Lookup)
SELECT * FROM Northwind.dbo.Orders
WHERE CustomerID='VINET'

-- 觀察：last_user_seek、last_user_scan、last_user_lookup 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

--05 重建資料表 Orders 上的索引 PK_Orders
ALTER INDEX [PK_Orders] ON [dbo].[Orders]
REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO

-- 觀察：last_system_scan 等資料行
SELECT db_name(database_id) N'資料庫名稱',object_name(dm.object_id) N'資料表或檢視', inx.name N'索引',inx.type_desc N'索引類型的描述', dm.*
FROM sys.dm_db_index_usage_stats dm INNER JOIN sys.indexes inx
ON dm.object_id = inx.object_id AND dm.index_id = inx.index_id
WHERE database_id=db_id('Northwind') and object_name(dm.object_id)='Orders'

/*=========================================================*
-- 初始化計數器 sys.dm_db_index_usage_stats 為空值
/*
只要一啟動 SQL Server (MSSQLSERVER) 服務，計數器就會初始化為空值。
此外，每當卸離或關閉資料庫時 (例如，因為 AUTO_CLOSE 設為 ON)，就會移除所有與資料庫關聯的資料列。
*/
USE [master]
GO
ALTER DATABASE [Northwind]
SET AUTO_CLOSE ON WITH NO_WAIT
GO
WAITFOR DELAY '00:00:10';
GO
ALTER DATABASE [Northwind]
SET AUTO_CLOSE OFF WITH NO_WAIT
GO
USE [master]
GO

/*=========================================================*/
-- 查詢每一個資料庫與索引作業相關的計數器資料
SELECT db_name(database_id) N'資料庫', *
FROM sys.dm_db_index_usage_stats
ORDER BY 1

參考資料：
sys.dm_db_index_usage_stats (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188755.aspx