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

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Примечание.

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

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

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

Настройка Description По умолч. Комментарий
MAX_STORAGE_SIZE_MB Предельный размер пространства данных, которое хранилище запросов использует в базе данных клиента 100 до SQL Server 2019 (15.x)
1000 начиная с SQL Server 2019 (15.x)		 Принудительно для новых баз данных
INTERVAL_LENGTH_MINUTES Определяет время, в течение которого объединяются и сохраняются собранные статистические данные среды выполнения по планам запросов. Для каждого активного плана запроса в течение периода, заданного в этом параметре, будет сохраняться только одна строка. 60 Принудительно для новых баз данных
STALE_QUERY_THRESHOLD_DAYS Политика очистки на основе времени, которая контролирует срок хранения статистики для среды выполнения и неактивных запросов. 30 Принудительно для новых баз данных и баз данных с предыдущим значением по умолчанию (367)
SIZE_BASED_CLEANUP_MODE Указывает, нужно ли выполнять автоматическую очистку данных при приближении к предельному значению, установленному для размера данных хранилища запросов. АВТОМАТИЧЕСКИ Принудительно для всех баз данных
QUERY_CAPTURE_MODE Указывает, следует ли отслеживать все запросы или только определенное подмножество. АВТОМАТИЧЕСКИ Принудительно для всех баз данных
DATA_FLUSH_INTERVAL_SECONDS Указывает максимальный период, в течение которого статистика среды выполнения будет храниться в памяти перед записью на диск. 900 Принудительно для новых баз данных

Важно!

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

Примечание.

хранилище запросов нельзя отключить в одной базе данных и эластичном пуле База данных SQL Azure. При исполнении 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.

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

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

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

Рекомендация Параметр
Ограничение сохраненных исторических данных. Настройте политику на основе времени для активации автоматической очистки.
Отфильтровывайте несущественные запросы. Настройте автоматическийрежим записи хранилища запросов.
Удаляйте менее важные запросы по достижении максимального размера. Активируйте политику очистки на основе размера.

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

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

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

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

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

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

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

Использование последней версии SQL Server Management Studio (SSMS)

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

  1. В среде SQL Server Management Studio обозреватель объектов щелкните правой кнопкой мыши базу данных.
  2. Выберите Свойства.
  3. Выберите хранилище запросов. На странице хранилище запросов убедитесь, что режим операции (запрошено) — запись считывания.
  4. Измените режим записи хранилище запросов на настраиваемый.
  5. Обратите внимание, что четыре поля политики записи в разделе хранилище запросов Политика отслеживания теперь включены и настраиваются.

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

В следующем примере 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_МБ.

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

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

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

Примечание.

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

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

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

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

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

Использование последней версии SQL Server Management Studio (SSMS)

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

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

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

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. Если размер хранилища запросов превышает квоту, хранилище переходит в режим "только чтение" (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 минут.

Примечание.

Описанное в этом разделе измерение параметров конфигурации Хранилища запросов не поддерживается для 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, чтобы удалить отдельный план.

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