傳回不同類型索引作業的計數,以及每種類型作業上次執行的時間。
在指定索引上由一項查詢執行所進行的每個個別搜尋、掃描、查閱或更新,在這份檢視都被當作使用一次該索引, 並且累加對應的計數器。
它會針對由使用者提交之查詢所導致的作業,以及由內部產生之查詢所導致的作業 (例如,掃描以收集統計資料),來報告資訊。
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
沒有留言:
張貼留言