使用查詢存放區調整效能

適用於： SQL Server 2016 (13.x) 以上版本 Azure SQL DatabaseAzure SQL 受控執行個體Azure Synapse Analytics

SQL Server 查詢存放區功能可讓您透過 SQL Server Management Studio 視覺化介面和 T-SQL 查詢，來探索和微調工作負載中的查詢。 本文詳細說明如何運用可採取動作的資訊來改善資料庫中的查詢效能，包括如何根據查詢的使用狀況統計資料和強制計劃來識別查詢。 您也可以使用查詢存放區提示功能來識別查詢並製作其查詢計劃，而不需要變更應用程式的程式碼。

• 若想瞭解收集此資料的詳細資訊，請參閱如何查詢存放區收集資料。
• 如需設定和管理查詢存放區的詳細資訊，請參閱使用查詢存放區監視效能。
• 如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱 操作 Azure SQL Database 中的查詢存放區。

效能微調範例查詢

查詢存放區會保留整個查詢執行過程當中的編譯和執行階段度量歷程記錄，以讓您詢問關於工作負載的問題。

下列範例查詢有助於效能基準和查詢效能調查：

上一個在資料庫中執行的查詢

過去一小時內在資料庫上執行的最後 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;

執行計數

過去一小時內每個查詢的執行次數：

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;

最長平均執行時間

過去一小時內平均持續時間最高的查詢數目：

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;

具有多項計畫的查詢

具有多個計劃的查詢特別有趣，因為它們可能是因計劃選擇變更而效能回歸的候選專案。

下列查詢會識別過去一小時內計劃數目最高的查詢：

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;

下列查詢會識別這些查詢，以及過去一小時內的所有計劃：

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;

最長等候時間

此查詢會傳回前 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;

注意

在 Azure Synapse Analytics 中，本節中的 查詢存放區 範例查詢除了等候統計數據除外，這些統計數據不適用於 Azure Synapse Analytics 查詢存放區 DMV。

最近效能迴歸的查詢

下列查詢範例會傳回過去 48 小時內因計劃選擇變更而重複執行時間的所有查詢。 此查詢會並列比較所有執行階段：

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 小時) 與歷程記錄期間 (前一天) 的執行，並找出因 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時，您只能強制 查詢存放區 記錄的計劃做為該查詢的計劃。 換句話說，查詢唯一可用的計劃是已在使用中 查詢存放區 時執行該查詢的計劃。

注意

Azure Synapse Analytics 不支援 查詢存放區 中的強制方案。

規劃強制支援快速向前和靜態數據指標

在 SQL Server 2019 （15.x） 和更新版本中，以及 Azure SQL 資料庫 （所有部署模型），查詢存放區 支援強制查詢執行計劃以快速向前和靜態 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)
• 效能的監視與微調
• 效能監視及微調工具
• 查詢存放區提示
• 使用查詢存放區的工作負載和 Database Engine Tuning Advisor 微調資料庫