我想找出数据库中哪些表使用最频繁(我的意思是读/写字节/表大小比率)或 IO/表大小比率.决定将哪些表放在较快的磁盘上,哪些放在较慢的磁盘上.有什么想法吗?
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?
我尝试使用 dm_db_index_usage_stats
但是如何找出单次搜索/扫描下有多少IO?
I tried make use of dm_db_index_usage_stats
But how to find out how many IO are under single seek/scan?
提前致谢.
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)
顺便说一句,需要检查的是免费的 Red-Gate 脚本管理器,它有一系列用于诊断信息的 SQL 脚本(不,我不为它们工作).
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).
SQL 脚本管理器
这篇关于Sql server 表使用情况统计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!