都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

設定の内容とか、ログの使用量とかのデータベースの状態を把握してみる

 今日は休養のため、お仕事をお休みしています。関東地方は台風の影響もなく、すっかり晴れてます☀

f:id:koogucc11:20140711120553p:plain

 今回は、データベースの色々な状態把握に関して記事にしたいと思います。データベースの設定情報を参照するのであれば、sys.databases です。SQL Server Management Studio で下記の SQL を実行してみましょう。

select * from sys.databases

f:id:koogucc11:20140708184029p:plain

 データベースの互換性レベル、照合順序、スナップショット分離状態、復旧モデル、暗号化の状態など、データベースに関する情報を取得することが可能です。

 データベース状態として知りたいこととしては、トランザクションログの容量が気になります。( 個人的に運用でログが溢れてシステム止まったりしたことがあったので.... ) パフォーマンスカウンタから取得できるのですが、DMV を使うのであれば sys.dm_os_performance_counters が良いでしょう。SQL Server Management Studio で下記の SQL を実行してみましょう。SQL Server に関するパフォーマンスカウンタが取得できます。

select * from sys.dm_os_performance_counters

f:id:koogucc11:20140708190144p:plain

  トランザクションログの使用率を求める式は下記の通りです。( 式です!っていうほどのものじゃないですねww )

トランザクションログ使用率 = (トランザクションログの使用量 / トランザクションログの総量) * 100

 よって、sys.dm_os_performance_counters から、『Log File(s) Size (KB)%』および『Log File(s) Used Size (KB)%』の値を取得し、計算する必要があります。さらに、SQL Server のチューニングについてまとめてみる - その3 - ( パフォーマンスがどれくらい改善される? ) - 都内で働くSEの技術的なひとりごと 取り上げたデータベースごとにインデックスがない件数も付加してみましょう。( ほとんど遊んでますねwww )

SELECT DB_NAME(database_id) as [データベース名],
count(*) as [期待しているインデックスが存在しない数]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)

 全部まぜまぜすると、下記のようなクエリのできあがりです。データベースの設定情報、状態、インデックスがない数といったものが一度にわかってしまいます。( 最近、[] = '列名' という書き方に変更しています。なんとなくこちらのほうが書きやすいので。) 下記のクエリを SQL Server Management Studio で実行してみましょう。

SELECT [データベース名] = db.name, 
[リカバリモデル] = db.recovery_model_desc,
[トランザクションログサイズ(KB)] = opc_ls.cntr_value,
[トランザクションログ使用量(KB)] = opc_lus.cntr_value,
[トランザクションログ使用率(%)] = CAST(CAST(opc_lus.cntr_value AS FLOAT) / CAST(opc_ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100,
[インデックスが存在しない数] = mic.missingidxcnt,
[互換性レベル] = (CASE db.compatibility_level
WHEN 70 THEN 'SQL Server 2008 - SQL Server 2008 R2'
WHEN 80 THEN 'SQL Server 2008 - SQL Server 2008 R2'
WHEN 90 THEN 'SQL Server 2008 - SQL Server 2012'
WHEN 100 THEN 'SQL Server 2008 - SQL Server 2014 and Azure AQL データベース'
WHEN 110 THEN 'SQL Server 2012 - SQL Server 2014'
WHEN 120 THEN 'SQL Server 2014 - SQL Server 2014'
END),
[統計情報の自動作成] = (CASE db.is_auto_create_stats_on
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
END),
[統計情報自動更新] = (CASE db.is_auto_update_stats_on
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
END),
[統計情報非同期更新]= (CASE db.is_auto_update_stats_async_on
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
END)
FROM sys.databases AS db
FULL OUTER JOIN (SELECT [dbname] = DB_NAME(database_id),
[missingidxcnt] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)) as mic
On db.name = mic.dbname
INNER JOIN sys.dm_os_performance_counters AS opc_lus
ON db.name = opc_lus.instance_name
INNER JOIN sys.dm_os_performance_counters AS opc_ls
ON db.name = opc_ls.instance_name
WHERE opc_lus.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND opc_ls.counter_name LIKE N'Log File(s) Size (KB)%'

f:id:koogucc11:20140711114205p:plain

 自分好みのクエリを作成するのも面白いですね。( 実行後、結果によっては、悲しくなってしまうかもしれませんが... )