問題描述

我想找出數(shù)據(jù)庫(kù)中哪些表使用最頻繁(我的意思是讀/寫字節(jié)/表大小比率)或 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)

順便說(shuō)一句，需要檢查的是免費(fèi)的 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 腳本管理器

這篇關(guān)于Sql server 表使用情況統(tǒng)計(jì)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！