動的管理ビューって便利そうだけど、沢山種類があるしカラムの意味を調べるのも結構大変ですよね。

動的管理ビューの概要はこちら
https://msdn.microsoft.com/ja-jp/library/ms188754(v=sql.120).aspx
動的管理ビューと動的管理関数では、サーバーの状態情報が返されます。返された情報は、サーバーインスタンスのヘルス状態の監視、問題の診断、パフォーマンスのチューニングに使用できます。
ということで、上手く使えばいろんな情報がサクッと取得できて嬉しいです。「○○するためのDMVを使ったSQL文」っていうまとまりで把握できてると便利だと思うので、僕がよく使っているクエリを紹介します。

そもそも自分の権限で動的管理ビューをSELECTできるかをチェック

動的管理ビューのSELECTには、VIEW SERVER STATE権限が必要なもの、VIEW DATABASE STATE権限が必要なものの二種類が存在します。下記のクエリを実行することで、自分がどの権限を持っているか確認できます。

-- DMVを見るための権限を持っているか確認するSQL
-- エラーでなければ、VIEW SERVER STATE権限を持っている
select top 10 * from sys.dm_tran_locks

-- エラーでなければ、VIEW DATABASE STATE権限を持っている
select top 10 * from sys.dm_db_partition_stats

権限が無ければ、下記SQLで付与できます。

    --dmv 権限付与(VIEW SERVER STATEを与えるとVIEW DATABASE STATE権限が必要なDMVもSELECTできるようになるみたいです）
    USE [master]
    GO
    GRANT VIEW SERVER STATE TO [ログイン名]
    GO

ここからは、目的別にDMVを使ったクエリを紹介します。

インデックスの断片化をチェックする

    DECLARE @DB_ID int, @OBJECT_ID int
    set @DB_ID = DB_ID('db名')
    set @OBJECT_ID = OBJECT_ID('table名')

    --avg_fragmentation_in_percent が断片化率
    SELECT
        *
    FROM sys.dm_db_index_physical_stats (@DB_ID, @Object_ID, NULL , NULL, 'DETAILED') as A
    JOIN sys.objects as B on A.object_id = B.object_id

インデックスの利用状況をチェック

    SELECT o.NAME AS table_name
        ,i.NAME AS index_name
        ,s.user_seeks -- Index Seek 数
        ,s.user_scans -- Index Scan 数
        ,s.user_updates -- UPDATE 数
        ,s.user_lookups -- lookup 数
        ,last_user_seek
        ,last_user_scan
        ,last_user_lookup
        ,last_user_update
    FROM sys.objects o
    INNER JOIN sys.indexes i ON o.object_id = i.object_id
    INNER JOIN sys.dm_db_index_usage_stats s ON i.index_id = s.index_id
    WHERE o.type = 'U'
        AND database_id = DB_ID()
        AND s.object_id = OBJECT_ID('Table名')
        AND o.NAME = 'Table名'
    ORDER BY s.user_seeks + s.user_scans DESC -- OrderByを変えることでScan多い順番とか、いろいろな順番で見れる

クエリキャッシュの利用状況をチェック

    SELECT TOP 100 *
    FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) ss
    --cross apply sys.dm_exec_query_plan(plan_handle) -- プランもみたいときはコメント外す
    CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) sb -- 細かいメタデータ見なくていいときはコメントアウト
    WHERE TEXT LIKE '%ストアド名など%' -- ここで絞込み
        AND ss.dbid = DB_ID('DB名') -- DBID指定

現在実行中のクエリ一覧を取得

出典:http://itpro.nikkeibp.co.jp/article/COLUMN/20070216/262179/?ST=win&P=2&rt=nocnt

    SELECT session_id
        ,TEXT
    FROM sys.dm_exec_requests s1
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
    ORDER BY TEXT

現在取得されているロック一覧を取得

    SELECT *
    FROM sys.dm_tran_locks
    WHERE resource_database_id = DB_ID('db名')
    -- AND request_mode <> 'S' -- 共有ロックだけ除外したりできる

ブロッキング検出

出典:http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

    SELECT db.NAME DBName
        ,tl.request_session_id
        ,wt.blocking_session_id
        ,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName
        ,tl.resource_type
        ,h1.TEXT AS RequestingText
        ,h2.TEXT AS BlockingTest
        ,tl.request_mode
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

以上、参考になるものがあれば幸いです！
他にも便利なクエリがあれば是非コメントで共有してくれると嬉しいです！

SQLServer: 知ってると便利なDMV(動的管理ビュー)まとめ