動的管理ビューって便利そうだけど、沢山種類があるしカラムの意味を調べるのも結構大変ですよね。
動的管理ビューの概要はこちら
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' -- 共有ロックだけ除外したりできる
ブロッキング検出
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
以上、参考になるものがあれば幸いです！
他にも便利なクエリがあれば是非コメントで共有してくれると嬉しいです！