SQLServerの動的管理ビューについて調べてみた


作りかけのツールで必要になったのでSQLServerの動的管理ビューについて調べてみました。
参考にしたのは以下のサイト。


いま作っているのは「特定のテーブルのデータをオンメモリ(キャッシュ)に配置するためにクエリーを投げるツール」なのですが、その効果を知るためにクエリーを投げる前後のデータキャッシュのサイズを取得する必要があってそれに使える機能がなにかないか調べていて動的管理ビューのことを教えてもらいました。


最終的には「SQL Server オペレーティング システム関連の動的管理ビュー (Transact-SQL)」のsys.dm_os_buffer_descriptorsを使って実現しました。

SELECT (COUNT(*) * 0.008024) AS cached_size ,name
  FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
 WHERE database_id = DB_ID()
 GROUP BY name, index_id 
 ORDER BY cached_pages_count DESC;


ほとんどサンプルのままなのですが、サンプルだと1列目のcached_sizeはページ数になってしまうので8KBをかけて1024で割ることでMB単位に変換しています。あと不要な列を削除したり。


この動的管理ビューはSQLServer2005からの機能だそうでして、SQLServer2014が出ていることを考えればかなり前からあるのに気付いていなかったことになります。もったいない。。。たいへん便利なのでこれからは積極的に使っていきたいです。