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

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

動的管理ビューの概要はこちら
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
```


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