Рекомендации по управлению хранилище запросов

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье описывается управление хранилище запросов SQL Server и окружающими функциями.

• Дополнительные сведения о настройке и администрировании с помощью хранилища запросов см. в разделе Мониторинг производительности с помощью хранилища запросов.

хранилище запросов по умолчанию в База данных SQL Azure

В этом разделе описаны оптимальные параметры конфигурации Базы данных SQL Azure по умолчанию, которые обеспечивают надежную работу хранилища запросов и зависимых компонентов. По умолчанию конфигурация оптимизирована для постоянного сбора данных, т. е. для минимальной продолжительности состояний "Отключено" и "Только для чтения". Дополнительные сведения обо всех доступных параметрах хранилища запросов см. в разделе ALTER DATABASE SET Options (Transact-SQL).

Если вы хотите сохранить свои пользовательские настройки, используйте параметры ALTER DATABASE для хранилища запросов , чтобы восстановить предыдущее состояние конфигурации. Изучите рекомендации по использованию хранилища запросов, чтобы научиться правильно выбирать оптимальные параметры конфигурации.

Установка оптимального режима записи для хранилища запросов

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

Храните наиболее важные данные в хранилище запросов

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

В следующей таблице приведены рекомендации.

Пользовательские политики отслеживания

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

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

Режим записи хранилище запросов указывает политику отслеживания запросов для хранилище запросов.

• Все: записывает все запросы. Этот параметр используется по умолчанию в SQL Server 2016 (13.x) и SQL Server 2017 (14.x).
• Авто: нечасто и запросы с незначительным длительностью компиляции и выполнения игнорируются. Пороговые значения для числа выполнений, длительности компиляции и времени выполнения определяются внутренним образом. Начиная с SQL Server 2019 (15.x), это параметр по умолчанию.
• Нет: хранилище запросов перестает записывать новые запросы.
• Пользователь: позволяет дополнительно контролировать и настраивать политику сбора данных. Новые пользовательские параметры определяют действия, которые выполняются при достижении внутреннего порогового значения времени для политики записи. Это временная граница, в пределах которой происходит оценка настраиваемых условий и, если какие-либо из них соблюдаются, разрешается запись запроса в хранилище запросов.

При настройке соответствующей настраиваемой политики записи для вашей среды следует учитывать следующее:

• База данных очень большая.
• База данных имеет большое количество уникальных нерегламентированных запросов.
• База данных имеет определенные ограничения размера или роста.

Примеры пользовательских политик отслеживания

В следующем примере QUERY_CAPTURE_MODE устанавливается значение AUTO и настраивается настраиваемый режим записи. Каждая из следующих политик настраивает политики отслеживания значением по умолчанию в SQL Server 2022 (16.x). Рассмотрите возможность настройки этих значений, чтобы уменьшить количество захваченных запросов и, следовательно, уменьшить объем хранилище запросов на диске. Рекомендуется постепенно изменить эти значения на небольшие приращения.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Следующий пример запроса изменяет существующий хранилище запросов для использования пользовательской политики записи, которая переопределяет параметры по умолчанию для EXECUTION_COUNT иTOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

максимальный размер хранилище запросов

Максимальный размер хранилище запросов по умолчанию составляет 1000 МБ, начиная с SQL Server 2019 (15.x). В предыдущих версиях значение по умолчанию составило 100 МБ. Увеличение максимального предела размера хранилище запросов подходит в занятой базе данных с множеством уникальных планов запросов. Изменение политики записи (см. предыдущий раздел) является более важным фактором, чтобы ограничить размер хранилище запросов на диске и запретить хранилище запросов вводить режим READ_ONLY. По мере того как хранилище запросов собирает запросы, планы выполнения и статистику, его размер в базе данных растет, пока не будет достигнут этот предельный объем. В этом случае хранилище запросов автоматически изменяет режим работы на READ_ONLY и останавливает сбор новых данных, что означает, что анализ производительности больше не является точным.

• В SQL Server и Управляемый экземпляр SQL Azure MAX_STORAGE_SIZE_MB ограничение не применяется строго.
• В База данных SQL Azure максимально допустимое MAX_STORAGE_SIZE_MB значение равно 10 240 МБ.

Размер хранилища проверяется только в том случае, если хранилище запросов записывает данные на диск. Этот интервал задается параметром DATA_FLUSH_INTERVAL_SECONDS или параметром диалогового окна хранилища запросов Management Studio Интервал записи данных на диск.

• Значение по умолчанию — 900 секунд (или 15 минут).
• Если хранилище запросов нарушили MAX_STORAGE_SIZE_MB ограничение между проверками размера хранилища, он переходит в режим только для чтения.
• Если параметр SIZE_BASED_CLEANUP_MODE включен, также активируется механизм очистки для принудительного применения ограничения MAX_STORAGE_SIZE_MB.
  • После очистки достаточного пространства режим хранилище запросов автоматически переключится на режим READ_WRITE.

Дополнительные сведения см. в разделе ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Интервал очистки данных (минуты)

Интервал очистки данных определяет частоту перед сохранением статистики среды выполнения на диске. В SQL Server Management Studio значение составляет несколько минут, но в Transact-SQL оно выражается в секундах. Значение по умолчанию — 15 минут (900 секунд).

• Увеличение интервала очистки данных может снизить общее влияние операций ввода-вывода в хранилище хранилище запросов, но приведет к тому, что рабочая нагрузка ввода-вывода хранилища будет более острой, с меньшим, но более тяжелым воздействием на использование дисков. Рассмотрите возможность увеличить значение, если рабочая нагрузка не формирует большое количество различных запросов и планов или вы можете выделить больше времени для сохранения данных до отключения базы данных.
• Уменьшение интервала очистки данных уменьшает объем хранилище запросов данных, которые будут потеряны в случае завершения работы, потери питания или отработки отказа. Это также может сгладить влияние операций ввода-вывода хранилища из хранилище запросов, записав на диск чаще, но с меньшим количеством данных.

Изменение хранилище запросов по умолчанию

Настраивайте хранилище запросов в соответствии с вашей рабочей нагрузкой и требованиями к устранению проблем производительности. Параметры по умолчанию подходят для начального использования, но вам следует наблюдать за поведением хранилища запросов с течением времени и соответствующим образом настраивать его конфигурацию.

Просмотр хранилище запросов текущих параметров

Просмотрите текущие параметры хранилище запросов в SQL Server Management Studio (SSMS) или T-SQL.

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

Следующий скрипт задает новое значение максимального размера (МБ):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Используйте SQL Server Management Studio или Transact-SQL, чтобы задать другое значение для интервала записи данных на диск:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Интервал сбора статистики: определяет уровень детализации для собранных статистических данных среды выполнения, выраженный в минутах. Значение по умолчанию — 60 минут. Рекомендуется использовать меньшее значение, если требуется большая степень детализации или меньшее время на обнаружение и устранение проблем. Помните, что это значение напрямую влияет на объем данных в хранилище запросов. Чтобы задать другое значение для интервала сбора статистики, используйте SQL Server Management Studio или Transact-SQL:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Пороговое значение устаревших запросов (дни): политика очистки на основе времени, которая управляет периодом хранения сохраняемой статистики среды выполнения и неактивными запросами, выраженными в днях. По умолчанию для хранилища запросов настроено хранение данных в течение 30 дней, что может быть излишне долго для вашего сценария.

Старайтесь не хранить исторические данные, которые не планируется использовать. Это позволит снизить переходы в состояние только для чтения. Объем данных в хранилище запросов и время на обнаружение и устранение проблем будут более предсказуемыми. Используйте Management Studio или следующий сценарий для настройки политики очистки на основе времени:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Режим очистки на основе размера: указывает, выполняется ли автоматическая очистка данных, когда хранилище запросов размер данных приближается к ограничению. Активируйте очистку на основе размера, чтобы хранилище запросов всегда работало в режиме чтения и записи и собирало последние данные. Нет никаких гарантий в условиях тяжелых рабочих нагрузок, которые хранилище запросов очистка будет постоянно поддерживать размер данных в соответствии с ограничением. Возможно, что автоматическая очистка данных не будет успевать с обработкой и переключится (временно) в режим только для чтения.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

хранилище запросов режим записи: задает политику отслеживания запросов для хранилище запросов.

• Все: записывает все запросы. Этот параметр используется по умолчанию в SQL Server 2016 (13.x) и SQL Server 2017 (14.x).
• Авто: нечасто и запросы с незначительным длительностью компиляции и выполнения игнорируются. Пороговые значения для числа выполнений, длительности компиляции и времени выполнения определяются внутренним образом. Начиная с SQL Server 2019 (15.x), это параметр по умолчанию.
• Нет: хранилище запросов перестает записывать новые запросы.
• Пользователь: позволяет дополнительно контролировать и настраивать политику сбора данных. Новые пользовательские параметры определяют действия, которые выполняются при достижении внутреннего порогового значения времени для политики записи. Это временная граница, в пределах которой происходит оценка настраиваемых условий и, если какие-либо из них соблюдаются, разрешается запись запроса в хранилище запросов.

Следующий скрипт устанавливает параметр QUERY_CAPTURE_MODE в значение AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Примеры

В следующем примере QUERY_CAPTURE_MODE задано значение AUTO и заданы другие рекомендуемые параметры в SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

В следующем примере QUERY_CAPTURE_MODE задано значение AUTO и заданы другие рекомендуемые параметры в SQL Server 2017 (14.x), чтобы включить статистику ожидания:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

В следующем примере политика пользовательского захвата задает значение по умолчанию SQL Server 2019 (15.x) вместо нового режима автоматического отслеживания по умолчанию. Дополнительные сведения о параметрах политики пользовательской записи и параметрах по умолчанию см. в query_capture_policy_option_list><.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

обслуживание хранилище запросов

В этом разделе представлены некоторые рекомендации по управлению самой функцией хранилища запросов.

Состояние хранилища запросов

Данные в хранилище запросов содержатся в базе данных пользователя, поэтому их размер ограничен (настраивается с помощью MAX_STORAGE_SIZE_MB). Если размер данных в хранилище запросов достигнет предела, хранилище запросов автоматически изменит состояние с read-write на read-only и перестанет собирать новые данные.

Выполните запрос sys.database_query_store_options, чтобы определить, активно ли хранилище запросов, и узнать, собирается ли сейчас статистика среды выполнения.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Состояние хранилища запросов определяется по столбцу actual_state. Если состояние не соответствует требуемому, дополнительные сведения можно просмотреть в столбце readonly_reason. Если размер хранилища запросов превышает квоту, хранилище переходит в режим "только чтение" (read_only) и фиксирует причину. Сведения о причинах см. в sys.database_query_store_options.

Получение параметров запросов

Чтобы найти подробные сведения о состоянии хранилища запросов, выполните следующую команду в базе данных пользователя.

SELECT * FROM sys.database_query_store_options;

Задание интервала для хранилища запросов

Вы можете переопределить интервал для объединения статистики времени выполнения запросов (по умолчанию — 60 минут). Новое значение интервала находится в представлении sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Для параметра "INTERVAL_LENGTH_MINUTES" запрещены целочисленные значения. Используйте один из следующих интервалов: 1, 5, 10, 15, 30, 60 или 1440 минут.

Использование пространства в хранилище запросов

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

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Если хранилище запросов заполнено, используйте следующую инструкцию для его расширения.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Задание параметров хранилища запросов

Вы можете задать несколько параметров хранилища запросов одновременно с помощью одной инструкции ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Полный список параметров конфигурации см. в статье Параметры ALTER DATABASE SET (Transact-SQL).

Очистка пространства

Внутренние таблицы хранилища запросов создаются в группе файлов PRIMARY во время создания базы данных, и эту конфигурацию невозможно изменить позже. Если у вас не будет места, может потребоваться очистить старые хранилище запросов данные с помощью следующей инструкции.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

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

В Azure Synapse Analytics очистка хранилища запросов недоступна. Данные сохраняются автоматически за последние семь дней.

Удаление нерегламентированных запросов

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

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Вы можете определить собственную процедуру с другой логикой для очистки данных, которые вам больше не нужны.

В предыдущем примере используется sp_query_store_remove_query расширенная хранимая процедура для удаления ненужных данных. Кроме того, вы можете сделать следующее:

• Используйте sp_query_store_reset_exec_stats, чтобы удалить статистику времени выполнения для указанного плана.
• Используйте sp_query_store_remove_plan, чтобы удалить отдельный план.

Связанный контент

• Параметры ALTER DATABASE SET (Transact SQL)
• Представления каталога хранилища запросов (Transact-SQL)
• Хранимые процедуры хранилища запросов (Transact-SQL)
• Использование хранилище запросов с OLTP в памяти
• Руководство по архитектуре обработки запросов
• Указания хранилища запросов
• Мониторинг производительности с использованием хранилища запросов
• Настройка производительности с помощью хранилища запросов
• Хранилище и анализ исторических запросов в Azure Synapse Analytics