Лучшие практики по управлению хранилищем запросов

Статья
03/05/2025

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

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

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

Примечание.

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

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

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

Настройка	Описание	По умолчанию	Комментарий
MAX_STORAGE_SIZE_MB	Предельный размер пространства данных, которое хранилище запросов использует в базе данных клиента	Версия 100 до SQL Server 2019 (15.x) 1000 начиная с SQL Server 2019 (15.x)	Обязательный для новых баз данных
ДЛИТЕЛЬНОСТЬ_ИНТЕРВАЛА_МИНУТЫ	Определяет размер временного окна, в течение которого агрегируются и сохраняются собранные статистические данные времени выполнения по планам запросов. Для каждого активного плана запроса в течение периода, определенного этой конфигурацией, может быть только одна строка.	60	Применяется для новых баз данных
Порог_устарелого_запроса_дни	Политика очистки на основе времени, управляющая сроком хранения хранимой статистики выполнения и неактивных запросов.	30	Применяется принудительно для новых баз данных и баз данных с ранее установленным значением по умолчанию (367)
РЕЖИМ УБОРКИ НА ОСНОВЕ РАЗМЕРА	Указывает, нужно ли выполнять автоматическую очистку данных при приближении к предельному значению, установленному для размера данных хранилища запросов.	АВТО	Принудительно для всех баз данных
РЕЖИМ_ЗАХВАТА_ЗАПРОСОВ	Указывает, следует ли отслеживать все запросы или только определенное подмножество.	АВТО	Принудительно для всех баз данных
DATA_FLUSH_INTERVAL_SECONDS	Указывает максимальный период, в течение которого статистика среды выполнения будет храниться в памяти перед записью на диск.	900	Применяется принудительно для новых баз данных

Внимание

Эти настройки по умолчанию автоматически применяются на последнем этапе активации хранилища запросов в базе данных SQL Azure. После включения База данных SQL Azure не изменит значения конфигурации, заданные клиентами, если они не влияют на основную рабочую нагрузку или надежные операции Хранилища запросов.

Примечание.

Хранилище запросов нельзя отключить в одиночной базе данных и эластичном пуле Azure SQL Database. При выполнении ALTER DATABASE [database] SET QUERY_STORE = OFF будет возвращено предупреждение 'QUERY_STORE=OFF' is not supported in this version of SQL Server..

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

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

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

Режим записи хранилища запросов	Сценарий
Все	Тщательно анализируйте рабочую нагрузку на основе форм всех запросов и частоты их выполнения, а также других статистических данных. Определите новые запросы в вашей рабочей нагрузке. Обнаруживайте, используются ли динамические запросы для определения возможностей для пользовательской или автоматической параметризации. Примечание. Это режим записи по умолчанию в SQL Server 2016 (13.x) и SQL Server 2017 (14.x).
Автоматически	Сосредоточьте внимание на важных и действенных запросах. Примерами могут служить запросы, которые выполняются регулярно или потребляют ресурсы в значительных объемах. Примечание. В SQL Server 2019 (15.x) и более поздних версиях это режим записи по умолчанию.
Не допускается	Вы уже записали набор запросов, который хотите отслеживать в среде выполнения, и хотите исключить отвлекающие факторы, которые могут быть внесены другими запросами. None не подходит для тестовых и эталонных сред. Кроме того, значение None подходит для поставщиков программного обеспечения, поставляющих конфигурацию хранилища запросов, настроенную для наблюдения за рабочей нагрузкой приложений. Значение None следует использовать с осторожностью, поскольку можно упустить возможность отслеживания и оптимизации важных новых запросов. Старайтесь не использовать None, если этого не требуется в конкретном сценарии.
Настраиваемое	SQL Server 2019 (15.x) представил настраиваемый режим записи в команде `ALTER DATABASE ... SET QUERY_STORE` . Хотя функция "Авто" используется по умолчанию и рекомендуется, если все еще есть обеспокоенность относительно затрат, которые может вызвать хранилище запросов, администраторы баз данных могут использовать пользовательские политики записи для дальнейшей настройки его поведения захвата. Для получения дополнительной информации и рекомендаций см. раздел Пользовательские политики записи далее в этой статье. Дополнительные сведения об этом синтаксисе см. в разделе "Параметры ALTER DATABASE SET".

Примечание.

Курсоры, запросы в хранимых процедурах и скомпилированные в собственном коде запросы всегда записываются, если задан режим записи в хранилище запросов All, Auto или Custom. Чтобы записывать скомпилированные нативные запросы, нужно включить сбор статистики на уровне запроса с помощью хранимой процедуры sys.sp_xtp_control_query_exec_stats.

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

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

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

Лучшие практики	Настройка
Ограничение сохраненных исторических данных.	Настройте временную политику для включения автоматической очистки.
Отфильтровывайте несущественные запросы.	Настройте автоматическийрежим записи хранилища запросов.
Удаляйте менее важные запросы по достижении максимального размера.	Включите политику очистки на основе размера.

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

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

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

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

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

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

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

SSMS
T-SQL

Скачайте последнюю версию SQL Server Management Studio (SSMS)

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

В обозревателе объектов в среде SQL Server Management Studio щелкните правой кнопкой мыши по базе данных.
Выберите Свойства.
Выберите хранилище запросов. На странице Query Store убедитесь, что Режим работы (Запрошенный) — Чтение и запись.
Измените режим захвата хранилища запросов на настраиваемый.
Обратите внимание, что четыре поля политики захвата в Хранилище запросов теперь включены и настраиваемы.

Используйте ALTER DATABASE .. Синтаксис SET для задания QUERY_CAPTURE_MODE = CUSTOM и указания параметров QUERY_CAPTURE_POLICY. Дополнительные сведения о параметрах настраиваемой политики отслеживания см. в следующих примерах T-SQL и параметрах ALTER DATABASE SET (Transact-SQL ).

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

В следующем примере 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 и Azure SQL Managed Instance 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 секунд).

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

Примечание.

С помощью флага трассировки 7745 можно предотвратить запись данных в хранилище запросов на диск в случае переключения из-за отказа или команды завершения работы. Дополнительные сведения см. в разделе "Использование хранилище запросов на критически важных серверах".

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

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

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

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

SSMS
T-SQL

Скачайте последнюю версию SQL Server Management Studio (SSMS)

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

В Обозревателе объектов SQL Server Management Studio щелкните правой кнопкой мыши на базе данных.
Выберите Свойства.
Выберите хранилище запросов.

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

Внимание

Курсоры, запросы в хранимых процедурах и скомпилированные в собственном коде запросы всегда записываются, если задан режим записи в хранилище запросов All, Auto или Custom. Чтобы записывать скомпилированные в собственном коде запросы, включите сбор статистики на уровне запроса с помощью хранимой процедуры sys.sp_xtp_control_query_exec_stats.

Следующий скрипт устанавливает параметр 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. Если размер Query Store превышает квоту, функция переключается в режим "только чтение" и указывает причину. Сведения о причинах см. в 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 минут.

Примечание.

Настройка параметров конфигурации Хранилища запросов, как показано в этом разделе, не поддерживается для Azure Synapse Analytics.

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

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

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, чтобы удалить отдельный план.

Поделиться через

Лучшие практики по управлению хранилищем запросов

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

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

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

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

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

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

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

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

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

Примеры

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

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

Получение опций Хранилища запросов

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

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

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

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

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

Обратная связь

Дополнительные ресурсы

Поделиться через

Лучшие практики по управлению хранилищем запросов

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

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

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

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

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

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

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

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

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

Примеры

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

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

Получение опций Хранилища запросов

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

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

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

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

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

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

Обратная связь

Дополнительные ресурсы