我想找出数据库中最常使用的表格(我指读/写字节/表格大小比率或IO/表格大小比率),以便决定哪些表格放在更快的磁盘上,哪些放在较慢的磁盘上。您有什么建议吗?
我试图利用dm_db_index_usage_stats
但如何找出单次查找/扫描下有多少IO?
提前致谢。
我想找出数据库中最常使用的表格(我指读/写字节/表格大小比率或IO/表格大小比率),以便决定哪些表格放在更快的磁盘上,哪些放在较慢的磁盘上。您有什么建议吗?
我试图利用dm_db_index_usage_stats
但如何找出单次查找/扫描下有多少IO?
提前致谢。
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脚本(不是我工作的公司)。
sys.dm_db_index_operational_stats
可能更有用。 - Martin Smith