はじめに

この投稿はアイスタイルアイスタイル Advent Calendar 2016の25日目の記事です。

なんだか、皆さん遠慮しがちで最終日だけポカーンと空いていて、「どうするどうするー？」なんて言っていたら、

某新人さん「こんな時こそchoiceですよ！」
ワタシ「（みんな大変そうだなぁ）」
hubotさん「厳正な抽選の結果、@sugatに決まりました」
ワタシ「えぇぇぇぇ」

というお約束的なパターンでトリを務めさせていただきます。
9月に入社した新米DBAの@sugat1679です。よろしくお願いします。

最終日の記事ということで、（主に内部の方々の）期待が大きくなりつつありますが、
DBAですし、SQL Serverの監視ツールについてなんぞをお話していきます。

ところで、今日は12月25日。クリスマスですね！
リア充のみなさん、

「きのうは、おたのしみ　でしｔ(ry」
(言ってて寂しくなってくるからやめよう)

背景

前職でも社内SEとしてデータベースの管理を中心にお仕事していましたが、
DBサーバの監視ツールが使いづらいものが多く、「これ！」という良いツールが見つからなかったので
自作してしまいました。
今回は、そのご紹介です。

DBサーバの監視ツールって

基本的にはどのツールも機能的には遜色のないものになっていて、
・リソース監視
・クエリー監視
・ブロッキング監視
等がリアルタイムで可視化されて、1台のサーバーの情報が細かく見れるものが多いです。
有名なところだと、DELL社のSpotlightとか、インサイトテクノロジー社のPISOとかがそれですね。

でも…

DBサーバの運用って、1台で行われるわけではないですし、更新系と参照系に分かれていて
レプリケーション等の同期ツールでデータを同期したり、ミラーリングやクラスタリングで
冗長化構成を取っていたりして、サーバ1台だけ深く見ていっても解決できない問題が多いのが
現状だと思います。
そして、

それ（監視ツール）、お高いでしょう？

DBサーバのトラブル対応って

大きく分けて、以下の2つに分類されます。
・レスポンスが悪い
→重たいクエリが実行中でサーバーが高負荷状態
→予期せぬ時間帯にデータが大量に流入
→誰かがテーブルロックしちゃってる
→いつもはすぐに返ってくるクエリが返ってこなくなった

・サーバーにつながらない
→CPU使用率100%のまま張り付き状態
→ディスクがいっぱいで書き込みできない
→497日問題(Windows Server 2008とか)

トラブル発生！最初にやること

私は、DBサーバのトラブルが発生した場合、以下の3つを確認します。

サーバーのリソース(CPU / メモリ / HDD)の状態を確認
実行中のクエリを確認
関係するサーバーの状態を確認

なので、1台のサーバーの深い情報は必要ではなく、複数台のサーバーにおいてどんな状態なのか？が
一目で確認できるツールが欲しい、と考えました。

そこで生まれたのは、自作の監視ツール「DBKeeper」

昨今のDB監視ツールについて調査していたところ、どれもサーバ1台に深く潜り込む物が多く、
自分の要望をかなえるツールが見当たりませんでした。
そしたら、誰かにささやかれたんです。
「じゃあ、自分で作っちゃえばいいじゃん」
と。
そうして生まれたのが、データベース監視ツール「DBKeeper」です。

サーバー1台あたりで「CPU」「メモリ」「キャッシュのヒット率」「ブロッキング」の状態がひと目にわかる情報を表示し、
それを4つの枠で一度に4台分のサーバーが監視できます。
プログラムは、C#.NetとWPF(Windows Presentation Foundation)というUIサブシステムで作りました。
1台分の枠をコントロール化させているので、画面の解像度やサイズによっていくつも増やせる仕様です。
画面のデザインは、車が好きなので車のメーターパネルを意識して作りました。

機能その１「ブロッキングツリー」

SQLServerはブロッキング（データの更新などでロックがかかってしまい、そのテーブルへのアクセス待ちが発生すること）が
よく発生するので、その事象を発生元から何の処理に影響を与えているのか、ツリー構造で表示させたいと思いました。
また、そのセッションの強制終了も可能です。

機能その２「セッションリスト」

SQLServer Management Studioでおなじみの「利用状況モニター」と同等のものです。
一度繋がったセッションのリストを表示できます。

機能その3「実行中のクエリリスト」

CPU負荷のグラフと一緒に、1秒おき、3秒おき、5秒おきに動的管理ビューを使って実行中のクエリを表示する、というものです。
主にパフォーマンス解析などに役立てます。

大きな機能としては、実はこの3つしか存在してません。
というのも、先程もお話したとおり、トラブル対応の初動捜査に必要なものはこれだけだからです。

※どうやら私はQiitaでの投稿が初の為、画像のアップロードに制限がかかっているようです。
もうちょっと画面を紹介しながら説明できれば…と思ったのですが、申し訳ございません。

使用しているSQLご紹介

画像が使えないっぽいので、せめてSQLだけでも紹介していきますね。

CPU使用率取得

パフォーマンスカウンタの値をSQLで取得するというちょっと変わった代物です。


-- ※10行目と15行目にはインスタンス名を入れること！！
select perfCount.object_name
     , perfCount.counter_name
     , perfCount.instance_name
     , case when perfBase.cntr_value = 0 then 0
       else ( cast ( perfCount.cntr_value as float ) / perfBase.cntr_value ) * 100
       end as cntr_Value
  from (
    select top 1 * from sys.dm_os_performance_counters
     where object_Name = 'SQLServer:Resource Pool Stats'
       and counter_name = 'CPU Usage %' ) perfCount
 inner join
 (
    select * from sys.dm_os_performance_counters
     where object_Name = 'SQLServer:Resource Pool Stats'
       and counter_name = 'CPU Usage % base' ) perfBase
    on perfCount.Object_name = perfBase.Object_name
   and perfCount.instance_name = perfBase.instance_name
 where perfCount.instance_name = 'internal';

メモリ使用率取得

これもまた、パフォーマンスカウンタの値をSQLで取得するというものです。
これは、SQLServer用に確保されているメモリの中で、さらに今の使用状況が見れるので、
インスタンス起動直後に見るとすごい低い数値なのですが、時間がたつにつれて上がっていく様子が見れるので
面白いです。


-- 8行目と12行目にはインスタンス名を入れること
SELECT a.cntr_value as MemoryInUse
    , (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS MemoryUseage
 FROM sys.dm_os_performance_counters a
 JOIN (SELECT cntr_value,OBJECT_NAME
         FROM sys.dm_os_performance_counters
        WHERE counter_name = 'Target Server Memory (KB)'
          AND OBJECT_NAME = 'SQLServer:Memory Manager'
     ) b
    ON a.OBJECT_NAME = b.OBJECT_NAME
 WHERE a.counter_name = 'Total Server Memory (KB)'
   AND a.OBJECT_NAME = 'SQLServer:Memory Manager'

キャッシュヒットレート(バッファーキャッシュ、プロシージャキャッシュ）

バッファーキャッシュヒットレート


-- 7行目と11行目にはインスタンス名を入れること
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
        FROM sys.dm_os_performance_counters
       WHERE counter_name = 'Buffer cache hit ratio base'
         AND OBJECT_NAME = 'SQLServer:Buffer Manager'
    ) b
   ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

プロシージャキャッシュヒットレート


-- 7行目と12行目にはインスタンス名を入れること
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS ProcedureCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
        FROM sys.dm_os_performance_counters
       WHERE counter_name = 'Cache Hit Ratio Base'
         AND OBJECT_NAME = 'SQLServer:Plan Cache'
         AND instance_name = 'SQL Plans'
    ) b
   ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Cache Hit Ratio'
  AND a.OBJECT_NAME = 'SQLServer:Plan Cache'
  AND a.instance_name = 'SQL Plans'

まとめ

サーバートラブルが起きると真っ先に疑われるのがデータベースであることが多いです。
実際、私の経験上でも、システムトラブルの大半がデータベース周りの何某かでした。
例えば…
・なんかめっちゃ重たいクエリ流れてる
→主な原因：インデックス不足、性能試験してませんでした
・毎日アクセスピーク時に必ずサーバーが応答なしになる
→主な原因：DBサーバのパラメータがデフォルトで運用してました
(コネクションプールがすぐにいっぱいになってた)

とかとか。

DBサーバの監視って大変だし、トラブルが起きるとエンジニアの技術力が問われるところでもあったりして、
属人化しやすい部分でもあります。
未然に防げればそれに越したことはないですが、やっぱり難しいのも現実問題です。
こうやってツールを作ったり創意工夫して、昨今のシステム運用を安全なものにしていきたいなーと思う
今日この頃でした。

それでは皆さん、良いお年を！