Настройка производительности с помощью хранилища запросов

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

Функция SQL Server хранилище запросов обеспечивает возможность обнаружения и настройки запросов в рабочей нагрузке с помощью визуального интерфейса SQL Server Management Studio и запросов T-SQL. В этой статье описаны способы повышения производительности запросов в базе данных, включая определение запросов на основе статистики использования и принудительных планов. Вы также можете использовать функцию подсказок хранилище запросов для идентификации запросов и формирования планов запросов без изменения кода приложения.

• Дополнительные сведения о сборе этих данных см. в статье о том, как хранилище запросов собирает данные.
• Дополнительные сведения о настройке и администрировании с помощью хранилища запросов см. в разделе Мониторинг производительности с помощью хранилища запросов.
• Сведения о работе с хранилищем запросов в базе данных SQL Azure см. в разделе Работа с хранилищем запросов в базе данных SQL Azure.

Примеры запросов на настройку производительности

Хранилище запросов ведет журнал компиляции и метрик выполнения во время выполнения запросов, что позволяет анализировать использование рабочей нагрузки.

Следующие примеры запросов могут быть полезны в базовой конфигурации производительности и исследовании производительности запросов:

Каковы последние запросы, выполненные в базе данных?

Последние 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;

Наибольшее среднее число операций чтения физических операций ввода-вывода

У скольких запросов самое высокое значение среднего количества операций чтения среди физических операций ввода-вывода за последние 24 часа с соответствующим числом строк и выполнений:

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;

Запросы, у которых недавно понизилась производительность

В следующем примере запроса возвращаются все запросы, для которых время выполнения удвоилось за последние 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вы можете принудительно заставить планы, записанные хранилище запросов в качестве плана для этого запроса. Другими словами, единственными планами, доступными для запроса, являются планы, которые уже использовались для выполнения этого запроса в то время как хранилище запросов был активным.

План форсирует поддержку для перемотки вперед и статических курсоров

В SQL Server 2019 (15.x) и более поздних версиях и База данных SQL Azure (все модели развертывания), хранилище запросов поддерживает возможность принудительного выполнения запросов для быстрых и статических курсоров 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)
• Наблюдение и настройка производительности
• Средства контроля и настройки производительности
• Указания хранилища запросов
• Настройка базы данных с помощью рабочей нагрузки из хранилище запросов с помощью помощник по настройке ядра СУБД