[SQL SERVER][TSQL]取得資料表讀寫計數

[SQL SERVER][TSQL]取得資料表讀寫計數

透過dmv 了解每個資料表讀寫狀況,後續可以微調索引填滿因子

透過下面TSQL即可取得相關資訊

 

DECLARE @mydbid int
SELECT @mydbid = db_id('AdventureWorks2012')

SELECT TableName = object_name(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @mydbid
GROUP BY object_name(s.object_id)
ORDER BY writes desc

 

image

 

 

參考

sys.dm_db_index_usage_stats