☰

最もコストのかかるクエリをログに記録して見つけるにはどうすればいいですか? 質問する

tag-icon • tag-icon sql-server sql-server-2008 profiling
最もコストのかかるクエリをログに記録して見つけるにはどうすればいいですか? 質問する

sql2k8 のアクティビティ モニターを使用すると、最もコストのかかるクエリを確認できます。これはすばらしいことですが、この情報をログに記録したり、クエリ アナライザーでこの情報を取得したりする方法はありますか? SQL 管理コンソールを開いてアクティビティ モニターのダッシュボードを見るのは、あまりしたくありません。

どのクエリが適切に記述されていないか、スキーマが適切に設計されていないかなどを把握したい。

ご協力いただければ幸いです。ありがとうございます!

ベストアンサー1

  1. SQL Server Profiler (SSMS のツール メニュー) を使用して、次のイベントをログに記録するトレースを作成します。

     RPC:Completed
 SP:Completed
 SP:StmtCompleted
 SQL:BatchCompleted
 SQL:StmtCompleted

  2. 標準のトレース テンプレートから始めて、それを整理することができます。これが特定のデータベース用かサーバー全体用かを指定していません。特定の Db 用である場合は、DatabaseID 列を含め、フィルターを DB ( SELECT DB_ID('dbname')) に設定します。各イベントに論理読み取りデータ列が含まれていることを確認します。トレースをファイルに記録するように設定します。このトレースをバックグラウンドで無人実行する場合は、十分なスペースがあれば、最大トレース ファイル サイズを 500 MB または 1 GB に設定することをお勧めします (サーバー上でのアクティビティの量によって異なるため、実際に試して確認する必要があります)。

  3. トレースを短時間開始し、一時停止します。[ファイル] -> [エクスポート] -> [スクリプト トレース定義] に移動して、DB バージョンを選択し、ファイルに保存します。これで、プロファイラー GUI を介して実行するよりもはるかに少ないオーバーヘッドでトレースを作成する SQL スクリプトができました。このスクリプトを実行すると、トレース ID (通常は@ID=2) が出力されるので、これを書き留めておきます。

  4. トレースファイル（.trc）を取得したら（最大ファイルサイズに達したためにトレースが完了したか、

    EXEC sp_trace_setstatus @ID, 0
    EXEC sp_trace_setstatus @ID, 2

トレースをプロファイラに読み込むか、クリアトレース(非常に便利です) または、次のようにテーブルにロードします。

SELECT * INTO TraceTable
FROM ::fn_trace_gettable('C:\location of your trace output.trc', default)

次に、次のようなクエリを実行してデータを集計できます。

SELECT COUNT(*) AS TotalExecutions, 
    EventClass, CAST(TextData as nvarchar(2000))
 ,SUM(Duration) AS DurationTotal
 ,SUM(CPU) AS CPUTotal
 ,SUM(Reads) AS ReadsTotal
 ,SUM(Writes) AS WritesTotal
FROM TraceTable
GROUP BY EventClass, CAST(TextData as nvarchar(2000))
ORDER BY ReadsTotal DESC

コストのかかるクエリを特定したら、実際の実行プランを生成して調べることができます。

おすすめ記事