SQL Server Profilerで高価なクエリを見つける

2024-04-08

SQL Serverで最も高価なクエリを特定することは、パフォーマンスの問題を診断し、データベースのパフォーマンスを向上させるために重要です。この目的のために、いくつかの方法があります。

方法

SQL Server Profiler

SQL Server Profilerは、SQL Serverに対するすべての活動をトレースするツールです。このツールを使用して、実行されたクエリ、実行時間、使用されたリソースなどの情報を収集できます。

手順

新しいトレースを作成します。
収集するデータを選択します。

利点

詳細な情報を収集できる
すべてのクエリをトレースできる

欠点

多くのオーバーヘッドが発生する
使用するのが複雑

クエリストア

クエリストアは、SQL Server 2012以降で導入された機能です。クエリストアは、実行されたクエリとその実行計画を保存します。この情報を使用して、最も高価なクエリを特定できます。

クエリストアを有効にします。
クエリの実行を待機します。
クエリストアからデータをクエリします。

簡単に使用できる

SQL Server 2012以降でのみ使用できる

拡張イベント

拡張イベントは、SQL Server 2008以降で導入された機能です。拡張イベントを使用して、特定の種類のイベントをトレースできます。

拡張イベントセッションを作成します。
セッションを開始します。

おすすめのツール

クエリストア：簡単に使用したい場合
拡張イベント：特定の種類のイベントをトレースしたい場合

注意事項

これらの方法は、SQL Serverのバージョンによって異なる場合があります。
詳細については、SQL Serverのドキュメントを参照してください。

補足

上記の方法以外にも、サードパーティ製のツールを使用して、最も高価なクエリを特定することもできます。
最も高価なクエリを特定したら、そのクエリを最適化する必要があります。

最適化の手順

インデックスを作成します。
クエリを書き換えます。

USE AdventureWorks2019

-- 新しいトレースを作成します
EXEC sp_trace_create @traceid = 1, @name = 'MyTrace'

-- 収集するデータを選択します
EXEC sp_trace_setevent @traceid = 1, @eventclass = 11, @includedcolumns = 0

-- トレースを開始します
EXEC sp_trace_start @traceid = 1

-- クエリを実行します
SELECT * FROM Person

-- トレースを停止します
EXEC sp_trace_stop @traceid = 1

-- トレース結果を分析します
EXEC sp_trace_getdata @traceid = 1

USE AdventureWorks2019

-- クエリストアを有効にします
ALTER DATABASE AdventureWorks2019 SET QUERY_STORE = ON

-- クエリの実行を待機します
WAITFOR DELAY '00:00:10'

-- クエリストアからデータをクエリします
SELECT qs.query_id, qs.total_execution_time, qs.last_execution_time, qs.text
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_execution_time DESC

USE AdventureWorks2019

-- 拡張イベントセッションを作成します
CREATE EVENT SESSION MySession ON SERVER 
ADD EVENT sqlserver.query_exec_duration 
(
    WHERE (
        (object_name(statement.objectid) = 'MyTable')
    )
)
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY = 1024
)

-- セッションを開始します
START EVENT SESSION MySession

-- クエリを実行します
SELECT * FROM MyTable

-- セッションを停止します
STOP EVENT SESSION MySession

-- セッション結果を分析します
SELECT * FROM sys.dm_xe_session_events
WHERE session_name = 'MySession'