问题描述
我想找出数据库中哪些表使用最频繁(我的意思是读/写字节/表大小比率)或 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 脚本管理器