DO's&DONT's #1: やらない方がいいこと – 運用環境で、Profiler GUI を使用してトレースする

神谷 雅紀
SQL Server Escalation Engineer

 SQL Server において、やらなければいけないこと / やってはいけないことを、Do's&Don't's として、シリーズで紹介したいと思います。初回の今回は、Profiler GUI についてです。

一定量の負荷がある環境において、Profiler GUI を用いてリアルタイムモニタをしたり、テーブルにトレースデータを保存したりすることは、パフォーマンスなどの問題を発生させる要因となります。絶対にやってはいけない、というほどではありませんが、ベストプラクティスとして、やらない方がよいことは確かです。

なぜ？

多数のクライアント接続があり、多数のリクエストを受け付けている SQL Server が生成するすべてのイベントをひとつの Profiler で受け取ることになるため、Profiler がボトルネックとなり、SQL Server 全体のパフォーマンスが著しく悪化し、実行中のクエリがタイムアウトする、ログインがタイムアウトする、場合によっては、クラスタやミラーリングのフェールオーバなどを招くこともあります。Profiler GUI は、ひとつの普通のクライアント接続を通じて、SQL Server からトレースイベントを受け取ります。当然、その接続で転送できる量は限られており、また、それをグラフィカル表示しなければならないため、SQL Server 側で生成されるイベント量が、Profiler が処理できるイベント量の限界を超える状況になると、SQL Server 側では新たなイベントを Profiler に渡せなくなり、イベントを生成している一般のクライアントは、イベント書き込み待ち状態になります。

さらに、Profiler を経由して SQL Server のテーブルへイベントを書き込んでいる場合は、SQL Server → Profiler → SQL Server とイベントデータが渡されることになり、パフォーマンスへの影響はより大きくなります。

テスト環境など負荷を制御できる環境や、非常に負荷の低く、ほとんど処理が行われていないような環境を除いて、何らかのトラブルシューティングのために Profiler GUI を用いて情報を採取することは避けた方がよいでしょう。

では、どうするか？

SQL Server 側でトレースイベントをファイルに直接書き出す機能 (SQL Trace/SQL トレース、もしくは、Server Trace/サーバートレース、Server-side Trace/サーバーサイドトレース と呼ばれる) を使用します。

SQL Trace は、sp_trace_* ストアドプロシージャを用いて、定義、実行開始/停止を行います。sp_trace_* ストアドプロシージャを使用した定義や開始のための T-SQL スクリプトは、Profiler GUI を用いて簡単に作成することができます。

SQL トレーススクリプトの作成、実行 (SQL Server 2008 R2, SQL Server 2008, SQL Server 2005)

SQLトレーススクリプトの作成、実行 (SQL Server 2000)

作成されたトレース結果を含むファイル (*.trc) は、Profiler GUI や fn_trace_gettable により参照可能です。