クエリ ストアを使用してパフォーマンスを調整する
適用対象:
SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
SQL Server クエリ ストア機能を使用すると、SQL Server Management Studio ビジュアル インターフェイスと Transact-SQL クエリを通じて、ワークロードでクエリを検出およびチューニングできます。 この記事では、使用統計情報に基づいてクエリを識別する方法やプランを強制する方法など、データベースのクエリ パフォーマンスを向上させるために実行可能な情報を取得する方法について詳しく説明します。 また、クエリ ストアのヒント機能を使用して、アプリケーション コードを変更せずにクエリを特定し、クエリ プランを作成することもできます。
- このデータの収集方法の詳細については、「クエリ ストアでデータを収集する方法」を参照してください。
- クエリ ストアを使用した構成と管理の詳細については、「クエリ ストアを使用したパフォーマンスの監視」を参照してください。
- Azure SQL Database におけるクエリ ストアの運用の詳細については、「 Azure SQL Database でクエリ ストアを運用する」を参照してください。
パフォーマンス チューニングのサンプル クエリ
クエリのストアには、コンパイルの履歴とクエリの実行全体に関するランタイム メトリックスが保持されており、ワークロードに関連した質問の答えを見つけることができます。
次のサンプル クエリは、パフォーマンス ベースラインとクエリ のパフォーマンス調査に役立ちます。
データベースで最近実行されたクエリ
過去 1 時間以内にデータベースで実行された最後 の n 個のクエリ:
SELECT TOP 10 qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;
実行回数
過去 1 時間以内の各クエリの実行回数:
SELECT q.query_id,
qt.query_text_id,
qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
qt.query_text_id,
qt.query_sql_text
ORDER BY total_execution_count DESC;
最長平均実行時間
過去 1 時間以内に平均期間が最も長いクエリの数:
SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
SUM(rs.count_executions) AS total_execution_count,
qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
GETUTCDATE() AS CurrentUTCTime,
MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id
ORDER BY avg_duration DESC;
物理 I/O 読み取りの平均が最も高い
過去 24 時間で平均物理 I/O 読み取り数が多かったクエリの上位と、対応する平均行数および実行カウント。
SELECT TOP 10 rs.avg_physical_io_reads,
qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
rs.runtime_stats_id,
rsi.start_time,
rsi.end_time,
rs.avg_rowcount,
rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;
複数のプランを持つクエリ
複数のプランを含むクエリは、プランの選択の変更によるパフォーマンス低下の候補になる可能性があるため、特に興味深いものです。
次のクエリは、過去 1 時間以内にプランの数が最も多いクエリを識別します。
SELECT q.query_id,
object_name(object_id) AS ContainingObject,
COUNT(*) AS QueryPlanCount,
STRING_AGG(p.plan_id, ',') plan_ids,
qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
q.query_id,
qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;
次のクエリは、過去 1 時間以内のすべてのプランと共にこれらのクエリを識別します。
WITH Query_MultPlans
AS (
SELECT COUNT(*) AS QueryPlanCount,
q.query_id
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
object_name(object_id) AS ContainingObject,
query_sql_text,
p.plan_id,
p.query_plan AS plan_xml,
p.last_compile_start_time,
p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
p.plan_id;
最長待機時間
このクエリは、過去 1 時間の待機時間が最も長い上位 10 個のクエリを返します。
SELECT TOP 10 qt.query_text_id,
q.query_id,
p.plan_id,
sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
q.query_id,
p.plan_id
ORDER BY sum_total_wait_ms DESC;
Note
Azure Synapse Analytics では、このセクションのクエリ ストアサンプル クエリは、待機統計を除き、Azure Synapse Analytics クエリ ストア DMV では使用できません。
最近パフォーマンスが低下したクエリ
次のクエリの例では、プランの選択が変更されたため、過去 48 時間に実行時間が 2 倍となったすべてのクエリが返されます。 このクエリでは、すべてのランタイム統計情報の時間間隔を並べて比較します。
SELECT qt.query_sql_text,
q.query_id,
qt.query_text_id,
rs1.runtime_stats_id AS runtime_stats_id_1,
rsi1.start_time AS interval_1,
p1.plan_id AS plan_1,
rs1.avg_duration AS avg_duration_1,
rs2.avg_duration AS avg_duration_2,
p2.plan_id AS plan_2,
rsi2.start_time AS interval_2,
rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
AND rsi2.start_time > rsi1.start_time
AND p1.plan_id <> p2.plan_id
AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
rsi1.start_time,
rsi2.start_time;
すべてのパフォーマンス回帰 (プラン選択の変更に関連する回帰だけでなく) を表示する場合は、前のクエリから条件 AND p1.plan_id <> p2.plan_id を削除します。
パフォーマンス低下の履歴を使用したクエリ
最近の実行と履歴実行を比較する場合、次のクエリは実行期間に基づいてクエリの実行を比較します。 この例では、クエリは、最近の期間 (1 時間) と履歴の期間 (過去 1 日間) とで実行を比較し、additional_duration_workload の原因となったものを識別します。 このメトリックは、最近の平均実行と履歴の平均実行に最近実行の数を掛けた値の間の差として計算されます。 これは、履歴と比較して、これらの最近の実行によって導入された余分な期間を表します。
--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;
SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();
--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;
SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();
WITH hist AS (
SELECT p.query_id query_id,
ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
SUM(rs.count_executions) AS count_executions,
COUNT(DISTINCT p.plan_id) AS num_plans
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_plan AS p
ON p.plan_id = rs.plan_id
WHERE (
rs.first_execution_time >= @history_start_time
AND rs.last_execution_time < @history_end_time
)
OR (
rs.first_execution_time <= @history_start_time
AND rs.last_execution_time > @history_start_time
)
OR (
rs.first_execution_time <= @history_end_time
AND rs.last_execution_time > @history_end_time
)
GROUP BY p.query_id
),
recent AS (
SELECT p.query_id query_id,
ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
SUM(rs.count_executions) AS count_executions,
COUNT(DISTINCT p.plan_id) AS num_plans
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_plan AS p
ON p.plan_id = rs.plan_id
WHERE (
rs.first_execution_time >= @recent_start_time
AND rs.last_execution_time < @recent_end_time
)
OR (
rs.first_execution_time <= @recent_start_time
AND rs.last_execution_time > @recent_start_time
)
OR (
rs.first_execution_time <= @recent_end_time
AND rs.last_execution_time > @recent_end_time
)
GROUP BY p.query_id
)
SELECT results.query_id AS query_id,
results.query_text AS query_text,
results.additional_duration_workload AS additional_duration_workload,
results.total_duration_recent AS total_duration_recent,
results.total_duration_hist AS total_duration_hist,
ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
SELECT hist.query_id AS query_id,
qt.query_sql_text AS query_text,
ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
ROUND(recent.total_duration, 2) AS total_duration_recent,
ROUND(hist.total_duration, 2) AS total_duration_hist,
recent.count_executions AS count_executions_recent,
hist.count_executions AS count_executions_hist
FROM hist
INNER JOIN recent
ON hist.query_id = recent.query_id
INNER JOIN sys.query_store_query AS q
ON q.query_id = hist.query_id
INNER JOIN sys.query_store_query_text AS qt
ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);
クエリのパフォーマンスの安定性を維持する
クエリが複数回実行された場合、SQL Server で使用されるプランが異なっており、リソースの使用率と期間が異なることがあります。 クエリ ストアを使用すると、クエリ パフォーマンスが低下している時点を検出し、対象期間の最適なプランを特定できます。 こうすることで、将来のクエリの実行で最適なプランを強制的に適用できます。
パラメーター (自動パラメーター化または手動パラメーター化) を使用して、クエリの一貫性のないクエリ パフォーマンスを識別することもできます。 さまざまなプランの中で、すべてのパラメーター値またはほとんどのパラメーター値に対して十分に高速で最適なプランを特定し、そのプランを強制することができます。 これにより、より広範なユーザー シナリオで予測可能なパフォーマンスが維持されます。
クエリに対してプランを強制する (強制ポリシーの適用)
特定のクエリに対してプランを強制すると、SQL Server 層のオプティマイザーでプランの強制が試みられます。 プランの強制が失敗した場合、拡張イベントが発生し、オプティマイザーは通常の方法で最適化するように指示されます。
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
使用sp_query_store_force_planする場合、そのクエリのプランとしてクエリ ストアによって記録されたプランのみを強制できます。 つまり、クエリで使用できるプランは、クエリ ストアがアクティブな間にそのクエリの実行に既に使用されていたプランだけです。
Note
クエリ ストアでのプランの強制は、Azure Synapse Analytics ではサポートされていません。
高速順方向カーソルと静的カーソルのサポートを強制するプラン
SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database (すべてのデプロイ モデル) では、クエリ ストアでは、高速かつ静的な Transact-SQL および API カーソルに対してクエリ実行プランを強制する機能がサポートされています。 強制は、sp_query_store_force_plan または SQL Server Management Studio のクエリ ストア レポートによってサポートされます。
クエリに対するプランの強制を解除する
もう一度 SQL Server クエリ オプティマイザーを利用して最適なクエリ プランを計算するには、クエリに対して選択されていたプランの強制を sp_query_store_unforce_plan を使用して解除します。
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;
関連するコンテンツ
- クエリ ストアを使用したパフォーマンスの監視
- クエリ ストアを使用する際の推奨事項
- インメモリ OLTP でのクエリ ストアの使用
- クエリ ストアの使用シナリオ
- クエリ ストアがデータを収集するしくみ
- クエリ ストアのストアド プロシージャ (Transact-SQL)
- クエリ ストアのカタログ ビュー (Transact-SQL)
- 利用状況モニターを開く方法 (SQL Server Management Studio)
- ライブ クエリ統計
- 利用状況モニター
- sys.database_query_store_options (Transact-SQL)
- パフォーマンスの監視とチューニング
- パフォーマンス監視およびチューニング ツール
- クエリ ストアのヒント
- データベース エンジン チューニング アドバイザーを使用したクエリ ストアからのワークロードを使用したデータベースのチューニング
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示