Optymalizacja wydajności za pomocą magazynu zapytań

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (tylko dedykowana pula SQL)bazy danych SQL w usłudze Microsoft Fabric

Funkcja magazynu zapytań programu SQL Server zapewnia możliwość odnajdywania i dostrajania zapytań w obciążeniu za pomocą interfejsu wizualnego programu SQL Server Management Studio i zapytań T-SQL. W tym artykule szczegółowo opisano, jak można wykorzystać informacje do podjęcia działań w celu zwiększenia wydajności zapytań w bazie danych, w tym jak identyfikować zapytania na podstawie ich statystyk użycia i wymuszania planów wykonania. Możesz również użyć funkcji hintów magazynu zapytań do identyfikowania zapytań i kształtowania ich planów bez zmieniania kodu aplikacji.

• Aby uzyskać więcej informacji na temat sposobu zbierania tych danych, zobacz Jak magazyn zapytań zbiera dane.
• Aby uzyskać więcej informacji na temat konfigurowania magazynu zapytań i administrowania nimi, zobacz Monitorowanie wydajności przy użyciu magazynu zapytań.
• Aby uzyskać informacje na temat obsługi magazynu zapytań w usłudze Azure SQL Database, zobacz Obsługa magazynu zapytań w usłudze Azure SQL Database.

Przykładowe zapytania dotyczące dostrajania wydajności

Sklep zapytań przechowuje historię metryk kompilacji i czasu wykonywania w trakcie wykonywania zapytań, co pozwala analizować swoje obciążenie.

Poniższe przykładowe zapytania mogą być przydatne do ustalenia podstawy wydajności i analizy wydajności zapytań.

Ostatnie zapytania wykonywane w bazie danych

Ostatnie n zapytania wykonywane w bazie danych w ciągu ostatniej godziny:

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;

Liczba wykonań

Liczba wykonań dla każdego zapytania w ciągu ostatniej godziny:

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;

Najdłuższy średni czas wykonywania

Liczba zapytań o najwyższym średnim czasie trwania w ciągu ostatniej godziny:

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;

Najwyższa średnia liczba fizycznych odczytów I/O

Liczba zapytań, które miały największą średnią liczbę fizycznych operacji we/wy odczytów w ciągu ostatnich 24 godzin, wraz z odpowiadającą średnią liczbą wierszy i liczbą wykonań:

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;

Zapytania z wieloma planami

Zapytania z więcej niż jednym planem są szczególnie interesujące, ponieważ mogą być kandydatami do regresji wydajności ze względu na zmianę wyboru planu.

Następujące zapytanie identyfikuje zapytania z największą liczbą planów w ciągu ostatniej godziny:

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;

Następujące zapytanie identyfikuje te zapytania wraz ze wszystkimi planami w ciągu ostatniej godziny:

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;

Najwyższe czasy oczekiwania

To zapytanie zwraca 10 pierwszych zapytań z najwyższymi czasami oczekiwania na ostatnią godzinę:

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;

Notatka

W Azure Synapse Analytics przykładowe zapytania z Query Store w tej sekcji są obsługiwane z wyjątkiem statystyk oczekiwania, które nie są dostępne w DMVs Query Store usługi Azure Synapse Analytics.

Zapytania, które ostatnio ulegają pogorszeniu wydajności

Poniższy przykład zapytania zwraca wszystkie zapytania, dla których czas wykonywania podwoił się w ciągu ostatnich 48 godzin z powodu zmiany wyboru planu. To zapytanie porównuje wszystkie interwały statystyk środowiska uruchomieniowego obok siebie:

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;

Jeśli chcesz zobaczyć wszystkie regresje wydajności (nie tylko regresje związane ze zmianą wyboru planu), usuń warunek AND p1.plan_id <> p2.plan_id z poprzedniego zapytania.

Zapytania z regresją historyczną w wydajności

Jeśli chcesz porównać ostatnie wykonanie z wykonaniem historycznym, poniższe zapytanie porównuje wykonanie zapytań w oparciu o okres wykonania. W tym konkretnym przykładzie zapytanie porównuje wykonanie w ostatnim okresie (1 godzina) z okresem historycznym (ostatni dzień) i wskazuje te, które wprowadziły additional_duration_workload. Ta metryka jest obliczana jako różnica między ostatnim średnim wykonaniem a średnim wykonaniem historii pomnożonym przez liczbę ostatnich wykonań. Przedstawia on dodatkowy czas trwania tych ostatnio wprowadzonych wykonań w porównaniu z historią:

--- "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);

Utrzymywanie stabilności wydajności zapytań

W przypadku zapytań wykonywanych wiele razy można zauważyć, że program SQL Server używa różnych planów, co powoduje różne wykorzystanie zasobów i czas trwania. Za pomocą Query Store można wykryć pogorszenie wydajności zapytań oraz określić optymalny plan w interesującym okresie. Następnie możesz wymusić ten optymalny plan na potrzeby przyszłego wykonywania zapytań.

Możesz również zidentyfikować niespójną wydajność zapytania dla zapytania z parametrami (automatycznie sparametryzowanymi lub ręcznie sparametryzowanymi). Wśród różnych planów można zidentyfikować plan, który jest wystarczająco szybki i optymalny dla wszystkich lub większości wartości parametrów i wymusić ten plan. Zapewnia to przewidywalną wydajność dla szerszego zestawu scenariuszy użytkownika.

Wymusić plan dla zapytania (stosowanie zasady wymuszania)

Gdy plan jest wymuszany dla określonego zapytania, program SQL Server próbuje wymusić plan w optymalizatorze. Jeśli wymuszanie planu zakończy się niepowodzeniem, zostanie wyzwolone zdarzenie rozszerzone, a optymalizator zostanie poinstruowany, aby zoptymalizować w normalny sposób.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Kiedy używasz sp_query_store_force_plan, możesz wymusić jedynie plany zarejestrowane przez Query Store jako plan dla tego zapytania. Innymi słowy, jedynymi planami dostępnymi dla zapytania są plany, które zostały już użyte do wykonania tego zapytania, gdy magazyn zapytań był aktywny.

Notatka

Wymuszanie planów w magazynie zapytań nie jest obsługiwane w usłudze Azure Synapse Analytics.

Planowanie wymuszania obsługi szybkich kursorów do przodu i statycznych

W programie Microsoft SQL Server 2019 (15.x) i nowszych wersjach oraz usłudze Azure SQL Database (wszystkie modele wdrażania) Query Store obsługuje możliwość wymuszania planów wykonywania zapytań do szybkiego przewijania oraz statycznych kursorów Transact-SQL i kursorów interfejsu API. Wymuszanie jest obsługiwane za pośrednictwem sp_query_store_force_plan lub raportów Query Store w programie SQL Server Management Studio.

Usuń wymuszanie planu dla zapytania

Aby ponownie użyć optymalizatora zapytań programu SQL Server w celu obliczenia optymalnego planu zapytania, użyj sp_query_store_unforce_plan, aby wymuś plan wybrany dla zapytania.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Powiązana zawartość

• Monitorowanie wydajności przy użyciu magazynu zapytań
• Najlepsze praktyki w magazynie zapytań
• korzystanie z magazynu zapytań z In-Memory OLTP
• scenariusze użycia magazynu zapytań
• jak magazyn zapytań zbiera dane
• procedury składowane magazynu zapytań (Transact-SQL)
• Widoki katalogu Query Store (Transact-SQL)
• Otwórz Monitor Aktywności (SQL Server Management Studio)
• statystyki zapytań na żywo
• Monitor aktywności
• sys.database_query_store_options (Transact-SQL)
• Monitorowanie i dostrajanie pod kątem wydajności
• narzędzia do monitorowania wydajności i dostrajania
• Wskazówki dotyczące Magazynu Zapytań
• Dostrajanie bazy danych z wykorzystaniem obciążenia z Query Store za pomocą Doradcy dostrajania silnika bazy danych