同期ミラーリング時に定期的に送信される内容について

Question

たびたび申し訳ありません。

自動フェールオーバを伴うミラーリングを構成して、実際に運用を開始したのですが、
どうも毎時00分付近に何らかのアクションが実行されて、
データベースへ一時的（数十秒）に高負荷を与えることがあるようです。
（というか毎時起きています）

なぜ、気づいたかというと、
アプリケーション側からのトランザクションが毎時同じ時間にのみタイムアウトの例外を出したため
パフォーマンスカウンタでSQL Serverの Lock BlocksとSQL Server:Mirror の Byte Sentをモニタしたところ、
上記のような事象が発覚しました。

メンテナンスプランなど、自身で作成したJOBを見直したり、
SQL Serverログ、エラーログ、イベントログなどもチェックしたのですが、
その時刻に何らかの操作を行っている形跡は全くありません。

この後はProfilerを使用してみようと思うのですが、トレースするべき内容や、
そもそもSQL Server内で何らかのJOBをバックグラウンドで実行していることがあるのか、
などなど、何らかの情報提供をいただけませんでしょうか。

お手数ですが、よろしくお願いします。

Answer 1

こんにちは、naginoです。

 

SQL Server は、確かに幾つか定期的な処理を行っています。

例えば、デッドロックの監視が 5秒間隔で行われていたり、フルテキストインデックスの再構築が 15分おきに実行されていたりします。

ただ、1時間おきというのは思い当たる処理がありません。

 

バックアップの取得や、定期レポートの生成バッチなど、何らかの処理を 1時間おきに行っていませんでしょうか。

個人的な感覚としては何らかの定期処理による負荷が原因の気がします。

 

Profiler は、バージョンによって機能や挙動に差がありますが、今回のように見当が付いていない状態であればスマートではありませんが、Stored Procedures の RPC:Completed や SP:Completed、TSQL の SQL:BatchCompleted や SQL: StmtCompleted あたりで処理を片っ端から捕捉し、負荷の高い処理を見つけるところからでしょうか。

ただ、負荷が本当に SQL Server によって発生しているのかどうか、負荷は CPU・ディスク IO・メモリ・ネットワークのどこにあるかを、事前にパフォーマンスモニタなどで切り分けされることをお勧めします。

 

なお、蛇足ながらOS、SQL Server のバージョン、エディション、SPや、ミラーリングの動作モードなどの情報は記載されたほうがより適切な返信がつきやすいかと思います。

もちろん、こういった場ですので返信があることが保証されるわけでもありませんし、守秘義務などによって一切口外できないケースもありますので、ケースバイケースではありますので、ご参考までということでお願いします。

 

ご参考になれば幸いです。

Answer 2

naganoさん、ご返信いただき、ありがとうございます。


まず、ご指摘いただきましたDBサーバの使用環境を以下に記載させていただきます。

　OS：　　　　　　Windows Server 2003　Standard Edition SP2
　CPU：　　　　　3.40GHz
　メモリ：　　　　 2.00GB
　SQL Server： Microsoft SQL Server Standard Edition SP2
　バージョン：　 9.00.3282.00
　DBファイル容量： データ→約6GB（空き9%）、ログ→約400MB（空き63％）

　構成：　　　　　ミラーリング動作モードは高可用性（自動フェールオーバ）
　　　　　　　　　 ミラーリングのプリンシパルに当たるDBから、
　　　　　　　　　 別サーバ（同一ネットワーク内）のDBサーバ（性能は同等）に
　　　　　　　　　 レプリケーションを組んでいます。
　JOB等：　　　 1週間に一度のフルバックアップ、1日に一度の差分バックアップ、
　　　　　　　　　 1時間に一度のトランザクションログバックアップ。

　従って、トランザクションログのバックアップが疑われますが、
　タイムアウト発生時刻とバックアップ時刻の間には相関は見られませんでした。


さらにプリンシパルサーバのパフォーマンスカウンタで

　SQL Server：　Locks-Lock Requests
　SQL Server：　Database Mirrorring-Bytes Sent
　PhysicalDisk： Disk Writes

をモニタしたところ、タイムアウトが発生した時刻で上記の3カウンタの増加が見られました。
また、SQL Serverの利用状況モニタでは、ログ出力を中断した、
という内容のメッセージが表示されたことがありました。


従って、ディスクへの定期的なログ出力が高負荷となっているのかなあ、
と簡単に想像してしまったのですが･･･

プロファイラのStored ProceduresやT-SQLでは、負荷が高い処理は見つかりませんでした。。。

Answer 3

こんにちは、naginoです。

 

パフォーマンス関連は、症状があってもそれが原因なのか、別の原因による結果なのかが分かりにくいことが多々あり、難しい問題です。

また、例えばカウンタの増加といっても誤差の範囲内なのかどうかという問題や、ディスクのアクセスランプの点滅感覚などそのときの目に見えるそのほかの情報が参考になっていたりするので、どうしてもテキストベースですと難しいところです。

 

これらを踏まえた上で、ディスクの定期的なログ出力が負荷の原因だとすると、負荷を処理できる環境を用意するしかないでしょう。

（ログの出力を軽減する方法としてはデータベースの復旧モデルがありますが、ミラーリングを組んでいる以上復旧モデルを変更できませんので）

つまるところ RAID 等による高速化でしょうか。

 

ちょっと気になるのが、「SQL Server： Database Mirrorring-Bytes Sent」が高い値を示したということです。

以下の URL にあるように、本来ミラーリングの負荷は「Transaction Delay」などのカウンタで判断するのですが、関係があるカウンタですので、ミラーリング関連で問題があるかもしれません。

http://msdn.microsoft.com/ja-jp/library/ms189931.aspx

 

あと、既にご確認済みだとは思いますが、レプリケーション関連で何らかのジョブが問題の時間に実行されていないかはご確認ください。

 

なお、以下の URL にどのカウンタを測定すべきか記載がありますので、システムモニタを使用される際はご参照ください。

http://msdn.microsoft.com/ja-jp/library/ms175903(SQL.90).aspx

http://msdn.microsoft.com/ja-jp/library/ms178072(SQL.90).aspx

http://msdn.microsoft.com/ja-jp/library/ms176018(SQL.90).aspx

 

また、以下に各カウンタの説明がまとまっていますので、測定されたカウンタを分析される際にあわせてご参照ください。

http://msdn.microsoft.com/ja-jp/library/ms190382(SQL.90).aspx

 

ご参考になれば幸いです。

Answer 4

こんにちは。中川俊輔 です。

naginoさん、詳細な回答ありがとうございます。

YOSHIKAZUさん、フォーラムのご利用ありがとうございます。
その後いかがでしょうか？
有用な情報と思われたため、naginoさんの回答へ回答済みチェックをつけさせていただきました。

今後ともフォーラムをよろしく願いします。
それでは！

---

マイクロソフト株式会社 フォーラム オペレータ 中川 俊輔