今日は、CodePlex で提供されている [SQL Live Monitor] と [Performance Analysis of Logs (PAL) Tool] を使用した、SQL Server のモニタリングとレポーティングについてまとめてみたいと思います。
■SQL Live Monitor
SQL Live Monitor を使用すると、SQL Server の現状の稼働状況を確認することができます。
[SQL Server] の欄に情報を取得する SQL Server 名 (既定のインスタンスの場合はサーバー名、名前付きインスタンスの場合はサーバー名インスタンス名) を入力して、[Start] をクリックすることで情報が取得できます。
パフォーマンスチューニングをするにあたって、いくつかの項目は手動で情報を取得する必要がありますが現状の状態を把握するのにはとても便利なツールだと思います。
データキャッシュ / プロシージャキャッシュのメモリ使用状況は Page Life Expectancy といった定番の情報をリアルタイムに見やすく確認することが可能です。
Cache Hit Ratio 系は SQL Server のサービスが最後に起動してからの情報なのであまり参考にならない可能性がありますが…。
矢印でデータの流れがわかるようになっており、[>->->] であれば、左の項目から右の項目に、[<-<-<-] であれば右の項目から左の項目に対してデータが流れていることになります。
たとえば、[Checkpoints/Sec] ですが、チェックポイントが発生するとダーティーページがデータファイルに対してフラッシュされます。
そのため、[SQL Memory] から [Disk Storage] に対してデータが流れることになります。この矢印の方向を理解しておくと SQL Server のデータの流れが分かるかと思います。
情報はパフォーマンスモニタから取得されており、以下のような項目を取得し、画面に表示を行っています。
DiskIdleTime = LogicalDisk% Idle Time TransactionsSec = DatabasesTransactions/sec CPUPercent = Processor% Processor Time CompilesSec = SQL StatisticsSQL Compilations/sec TargetMemory = Memory ManagerTarget Server Memory (KB) BufferCacheSize = Buffer ManagerDatabase pages LocksPerSecond = LocksLock Requests/sec UserConnections = General StatisticsUser Connections ProcedureCacheSize = Plan CacheCache PagesSQL Plans MemPagesSec = MemoryPages/sec ProcQueueLength = SystemProcessor Queue Length WorkTables = Access MethodsWorktables Created/sec msdtc = Exec StatisticsDistributed QueryExecs in progress |
手動でパフォーマンス監視をしたいときはこの項目をベースに必要となる項目を追加していくと楽かもしれないですね。
Live Monitor でリンクになっている箇所は情報をブレークダウンすることができます。
SQL Live Monitor は現在の使用状況を確認するだけでなく、ログを取得する機能を持っています。
[Option] ボタンを押すことで、ログ取得の設定をすることが可能です。
デフォルトではログ取得は有効になっていないのですが、
[Log Data for offline analysis (CSV)]
[Log Blocked Process Details (CSV)]
[Log Data for PAL Analysis]
を有効にすることで、ログを取得することが可能になります。ログですが、SQL Live Monitor を実行したフォルダに取得されます。
# Blocked Process はうまく取得できなかったのですが…。
[PAL Analysis] は PAL Tool で解析をするための取得データになり、このデータはパフォーマンスモニタを使用して取得されています。
設定を有効にすると、[mscounters] というユーザー定義のデータコレクタ セットが作成され、このコレクタ セットで PAL Tool で解析するためのパフォーマンス情報が取得されます。
この PAL Tool 用のデータですが以下の情報が取得されています。
Network Interface(*)Bytes Total/sec SystemProcessor Queue Length Processor(*)% Processor Time PhysicalDisk(*)% Idle Time LogicalDisk(*)% Idle Time MemoryFree System Page Table Entries SystemContext Switches/sec Process(*)Private Bytes MemorySystem Cache Resident Bytes Paging File(*)% Usage Process(sqlservr)% Privileged Time SQL StatisticsBatch Requests/sec Access MethodsForwarded Records/sec Buffer ManagerBuffer cache hit ratio General StatisticsLogins/sec LatchesLatch Waits/sec Memory ManagerMemory Grants Pending SQL StatisticsSQL Compilations/sec Locks(_Total)Lock Requests/sec |
このログを使用して PAL Tool でレポートを作成することが可能となります。
■Performance Analysis of Logs (PAL) Tool
PAL Tool はパフォーマンスモニタで取得した情報を解析しレポートを作成することができるツールになります。
# PowerShell 2.0 と .NET Framework 3.5 の Chart Control が必要になります。
Live Monitor では、PAL Tool の [Microsoft SQL Server 2005/2008] のテンプレートに合わせた情報を取得していますので、取得したデータをすぐに PAL Tool にかけてレポートを作成することが可能です。
レポートの作り方は簡単で、[Counter Log] から Live Monitor で取得したパフォーマンスモニタのログデータを選択し、
[Threshold File] から、[SQL Server 2005/2008] をテンプレートとして設定をし、
[Execute] から [Finish] をクリックすることでレポートを作成することが可能です。
そうすると裏で PowerShell が実行されレポートが作成されます。
PAL Tool の実行が完了するとこのようなレポートが生成されます。
# デフォルトだと PAL Tool を実行したユーザーの [ドキュメントPAL Reports] に出力されます。
CPU の使用率の警告やメモリの空きの警告など基本的な情報を出力してくれるほかに SQL Server 固有のレポートが生成されます。
たとえば、Page Life Expectancy は 300 以上を推移しているのが良好な状態というのが定番になっていますが、そのレポートに関しても作成がされています。
# 300 を超えていない場合は赤くなって警告のレポートが作成されます。
こちらは CPU の使用率のグラフになるのですが、80%~100% は注意が必要な範囲 (Critical) なので赤くなっているのが確認できます。
以下のグラフでは瞬間的に Clitical の領域に達していることが確認できます。
ただし、別のグラフ (Processor の Queue) では Warning / Critical には達していませんので瞬間的な CPU の負荷であって、CPU のスペックの限界までは達していない (処理能力には余裕がある) 状態であると考察することができます。
Live Monitor で取得したデータを基に SQL Server の状態と、サーバーの基本的な状態をレポートとして確認をできますので、複数のグラフを見比べサーバーの状態を確認することが可能となります。
パフォーマンス チューニングではベースラインとして現状を確認することが重要になりますので、これらのツールを組み合わせると一からベースライン情報の取得を考えなくてもよいので楽になるかと思います。
グラフをどう読み解いていくかが難しいところではあるのですが、最初の一歩としては Warning / Critical となっている箇所を注視して、他のグラフと重ね合わせていくとよいのかな~と。