Last active
May 29, 2016 09:01
-
-
Save alantsai/eafe4b72070cc1637b51fade4ca8f2da to your computer and use it in GitHub Desktop.
Find Index Usage of a Database since it start the server - 取得db自啟動來的索引使用量 - #sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @databaseId nvarchar(128); | |
SET @databaseId = 'dbname'; ---改成要查看的資料庫 | |
-- 用戶索引查找次數 -> 這個表示主動用where條件過濾的欄位 | |
-- 用戶索引被動查找次數 -> 這個表示被動做join的次數 | |
select db_name(database_id) as N'資料庫名稱', | |
object_name(a.object_id) as N'表名', | |
b.name N'索引名稱', | |
user_seeks N'用戶索引查找次數', | |
user_lookups N'用戶索引被動查找次數', | |
user_scans N'用戶索引掃瞄次數', | |
last_user_seek N'最後查找時間', | |
last_user_scan N'最後掃瞄時間', | |
rows as N'表中的行數' | |
from sys.dm_db_index_usage_stats a join | |
sys.indexes b | |
on a.index_id = b.index_id | |
and a.object_id = b.object_id | |
join sysindexes c | |
on c.id = b.object_id | |
where database_id=db_id(@databaseId) | |
and object_name(a.object_id) not like 'sys%' | |
order by user_seeks, user_lookups,user_scans,object_name(a.object_id) desc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment