[SQL Troubleshooting] 第3回 :パフォーマンスログの確認方法について

■初めに

パフォーマンスモニタが「夢のようなツール」ではないという点については、以前のポストでお伝えした通りとなります。では、具体的にどのようなことに利用できるのかについて、少しまとめていきたいと思います。

パフォーマンスログを採取することで、以下のような確認が行えると考えています。

1) 問題発生時のサーバの負荷状況の確認
2) サーバのリソース不足の兆候の確認
3) 閾値が決められるカウンタによる状況の監視

1) については、我々サポートが問題発生時にパフォーマンスログを必要とする理由となります。ある現象、例えば一定時間全体のクエリパフォーマンスが低下したような現象についてお問い合わせいただいた際に、参考となるカウンタを含むパフォーマンスログがあれば、どのようなカウンタに動きがあったかを確認することで、現象を予測していくことができます。また、他の情報から読み取れた内容を補足する資料としても、有効な情報となります。

2) については、「問題の兆候があるか」という点をパフォーマンスログから読み取ることを期待した確認となります。これは、多くのお客様が望まれることで、お問い合わせの際に何も問題が発生していない時間帯のパフォーマンスログの結果から、何か問題を示す兆候がないか確認を依頼されることが多々あります。これについては、残念ながら我々サポートがお客様のパフォーマンスログから、問題の兆候を正確に読み取ることは難しく、通常あまり有効な回答を行えておりません。この理由については、後述いたします。

3) については、分かり易い閾値が設けられるカウンタを利用した監視となります。例えば、ドライブの空き容量等が分かり易い例となります。

このエントリでは、上記 1) ～ 3) のうち、2) と 3) に含まれる内容について、もう少し掘り下げていきます。

■ベースラインの必要性

前述した通り、特に問題の発生していないある一定の期間のパフォーマンスログを提供いただき、「問題の兆候」の確認を依頼いただくことがあります。こうしたご依頼の際、我々は一般的にご提供いただいたパフォーマンスログから、何かを読み取ることは困難です。

それは、対象のシステムにおいて、どのようにカウンタが推移するのが平常時の動作であるか、我々は判断ができないからとなります。例えば、ある時間帯に CPU が他の時間より高くなっているとします(そして、その時間帯にお客様からは問題があったという報告はありません)。実際には、その時間帯はバッチ的な処理が流されており、それによってCPU やその他のカウンタが大きく動きが変わっているだけだとしても、我々にはそれを判断することができません。

こうした観点でパフォーマンスログを確認するためには、平常時に長期間パフォーマンスログを採取し、その内容を分析したベースラインを設定する必要があります。設定したベースラインから大きく外れる値について、問題を示すものなのかどうかを検討し、必要に応じて情報を採取し、対応を行います。

このベースラインの必要性については、SQL Server の Books Online にも下記のトピックにて説明しています。

パフォーマンスのベースラインの設定

なお、条件が変わっていくことで、設定したベースラインから値が外れていきます。ユーザ数やデータ量の増加等の要因も十分に考慮した上で、その値が問題を示すものなのかの判断が必要となります。

■ボトルネックの特定

ボトルネックの特定についても、SQL Server の Books Online トピックに記載があります。

ボトルネックの特定

上記のトピックで言及している 5 つの主な要素のうち、「メモリ使用量」、「CPU の使用率」、「ディスクの入力/出力 (I/O)」については、それぞれパフォーマンスモニタに関連するトピックが用意されています。以下がそれぞれのトピックへのリンクとなります。これらの情報が、参考となりますのでご確認ください。

- メモリ使用率の監視

- CPU 使用率の監視

- ディスクの使用量の監視

「メモリの使用率の監視」に記載のある、ページングについては十分注意をする必要があります。ページングが発生した場合、大きくパフォーマンスが低下します。

SQL Server 専用のサーバである場合、SQL Server 自身が大量のメモリを使用することでページングを引き起こすことがあります。この状況を回避するためには、"Max Server Memory サーバオプション"を適切に設定する必要があります。詳細につきましては、以前のポストを参照してください。

DO’s&DONT’s #12: やった方がいいこと - max server memory を設定する

また、SQL Server 専用のサーバである場合は、一般的にディスク I/O や CPU 使用率を押し上げているのは、SQL Server 自身です。このような状況の場合、これらのリソースを大量に消費するクエリを特定しチューニングすることで、状況が改善する可能性があります。この調査には、サーバトレースが有効な調査手段となります。サーバトレースについては、後日別のエントリを作成する予定です。

残りの要素である「ユーザー接続数」については、SQL Server: General Statistics オブジェクトの「User Connections」から確認可能です。

- SQL Server: General Statistics オブジェクト

例) 名前付きインスタンス “SQL2012” の General Statistics オブジェクト配下のカウンタをすべて追加した例

また、「ブロッキング ロック」に関しては、SQL Server: Wait Statistics オブジェクトの「Lock waits」カウンタが参考となりますが、同時にブロッキング情報を採取することが解決の近道となります。この情報採取についても、後日別のエントリを作成する予定です。

- SQL Server: Wait Statistics オブジェクト

 

例) 名前付きインスタンス “SQL2012” の Wait Statistics オブジェクト配下のカウンタを追加した例

★ SQL Server トラブルシューティング 6 回シリーズのご案内★ 本記事は、第 3 回目となります。他の記事は以下をご参照ください。 https://blogs.msdn.com/b/jpsql/archive/2012/03/30/sql-server-6.aspx 第1回 SQL Server のログ、イベントログの確認方法 (03/30 UP) 第2回 パフォーマンスログの採取方法 (04/20 UP) 第3回 パフォーマンスログの確認方法 (05/07 UP 、本記事) 第4回 サーバートレースの解析方法 1 (05/18 UP) 第5回 サーバートレースの解析方法 2 (2/18 UP) 第6回 ブロッキング情報の確認方法 (07/24 UP)