Sql server table usage statistics
17,213
Select object_schema_name(UStat.object_id)
+ '.' + object_name(UStat.object_id) As [Object Name]
,Case
When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null
Else Cast(Sum(User_Seeks + User_Scans + User_Lookups) As Decimal)
/ Cast(Sum(User_Updates
+ User_Seeks
+ User_Scans
+ User_Lookups) As Decimal(19,2))
End As [Proportion of Reads]
, Case
When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null
Else Cast(Sum(User_Updates) As Decimal)
/ Cast(Sum(User_Updates
+ User_Seeks
+ User_Scans
+ User_Lookups) As Decimal(19,2))
End As [Proportion Of Writes]
, Sum(User_Seeks + User_Scans + User_Lookups) As [Total Read Ops]
, Sum(User_Updates) As [Total Write Ops]
From sys.dm_db_Index_Usage_Stats As UStat
Join Sys.Indexes As I
On UStat.object_id = I.object_id
And UStat.index_Id = I.index_Id
Join sys.tables As T
On T.object_id = UStat.object_id
Where I.Type_Desc In ( 'Clustered', 'Heap' )
Group By UStat.object_id
Order By object_schema_name(UStat.object_id)
+ '.' + object_name(UStat.object_id)
Btw, something to check out is free Red-Gate's Script Manager which has a series of SQL scripts for diagnostic information (no I do not work for them).
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Author by
Admin
Updated on June 26, 2022Comments
-
Admin about 2 years
I want to find out which tables in the database are most heavily used (I mean read/write Bytes/Table Size ratio) or IO / Table size ratio. To decide which tables to place on faster discs and which on slower ones. Any ideas?
I tried make use of dm_db_index_usage_stats
But how to find out how many IO are under single seek/scan?Thank you in advance.