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

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

В этой статье приведены рекомендации по использованию хранилища запросов SQL Server с вашей рабочей нагрузкой.

Примечание

В Azure Synapse Analytics параметры конфигурации для хранилища запросов не поддерживаются.

Используйте последнюю версию SQL Server Management Studio

SQL Server Management Studio включает в себя набор пользовательских интерфейсов, предназначенных для настройки хранилища запросов и использования собранных данных о рабочей нагрузке. Скачайте последнюю версию Среда Management Studio здесь.

Краткое описание того, как использовать хранилище запросов в сценариях устранения неполадок, см. в блогах по хранилищу запросов Azure.

Используйте анализ производительности процессов в Базе данных SQL Azure

При запуске хранилища запросов в База данных SQL Azure можно использовать анализ производительности запросов для анализа потребления ресурсов в динамике. С помощью Среда Management Studio и Azure Data Studio можно получить подробные сведения о потреблении ресурсов (ЦП, памяти и подсистемы ввода-вывода) всеми вашими запросами, а анализ производительности запросов обеспечивает быстрый и эффективный способ определения их влияния на общее использование DTU для базы данных. Дополнительные сведения см. в разделе Анализ производительности запросов в базе данных SQL Azure.

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

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

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

Конфигурация Описание По умолчанию Комментарий
MAX_STORAGE_SIZE_MB Предельный размер пространства данных, которое хранилище запросов использует в базе данных клиента 100 Принудительно для новых баз данных
INTERVAL_LENGTH_MINUTES Определяет время, в течение которого объединяются и сохраняются собранные статистические данные среды выполнения по планам запросов. Для каждого активного плана запроса в течение периода, заданного в этом параметре, будет сохраняться только одна строка. 60 Принудительно для новых баз данных
STALE_QUERY_THRESHOLD_DAYS Политика очистки на основе времени, которая контролирует срок хранения статистики для среды выполнения и неактивных запросов. 30 Принудительно для новых баз данных и баз данных с предыдущим значением по умолчанию (367)
SIZE_BASED_CLEANUP_MODE Указывает, нужно ли выполнять автоматическую очистку данных при приближении к предельному значению, установленному для размера данных хранилища запросов. AUTO Принудительно для всех баз данных
QUERY_CAPTURE_MODE Указывает, следует ли отслеживать все запросы или только определенное подмножество. AUTO Принудительно для всех баз данных
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); — 100 МБ. Этого размера может быть недостаточно, если рабочая нагрузка создает большое количество различных запросов и планов или вы хотите хранить журнал запросов более длительный период времени. Начиная с версии SQL Server 2019 (15.x) значение по умолчанию равно 1 ГБ. Отслеживайте текущее использование пространства и увеличивайте максимальный размер (МБ) , чтобы предотвратить переход хранилища запросов в режим "только чтение".

Важно!

Ограничение Максимальный размер (МБ) не применяется строго. Размер хранилища проверяется только в том случае, если хранилище запросов записывает данные на диск. Этот интервал задается параметром Интервал записи данных на диск (в минутах) . Если хранилище запросов нарушило ограничение максимального размера между проверками размера хранилища, оно будет переведено в режим только для чтения. Если параметр Режим очистки на основе размера включен, также активируется механизм очистки для принудительного применения ограничения максимального размера.

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

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

Интервал записи данных на диск (в минутах) . Определяет частоту для сохранения статистики, собранной во время выполнения, на диск. Выражается в минутах в графическом пользовательском интерфейсе, и в секундах — в Transact-SQL. Значение по умолчанию — 900 секунд, то есть 15 минут в графическом пользовательском интерфейсе. Рассмотрите возможность увеличить значение, если рабочая нагрузка не формирует большое количество различных запросов и планов или вы можете выделить больше времени для сохранения данных до отключения базы данных.

Примечание

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

Используйте 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
    );

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

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

Как приступить к устранению проблем производительности запросов

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

Устранение неполадок хранилища запросов

Включите хранилище запросов с помощью Среда Management Studio, как описано в предыдущем разделе, или выполните следующую инструкцию Transact-SQL:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Сбор хранилищем запросов набора данных, который точно представляет рабочую нагрузку, может занять некоторое время. Обычно одного дня достаточно даже для очень сложных рабочих нагрузок. Однако вы можете приступить к исследованию данных и выявлению запросов, требующих вашего внимания, сразу после включения этой функции. Перейдите во вложенную папку Query Store в узле базы данных в обозревателе объектов Среда Management Studio, чтобы открыть представления по устранению неполадок для конкретных сценариев.

Представления хранилища запросовСреда Management Studio работают с набором метрик выполнения, каждая из которых выражается в виде какой-либо из следующих статистических функций.

SQL Server version Метрика выполнения Статистическая функция
SQL Server 2016 (13.x); CPU time (время ЦП), Duration (длительность), Execution count (число выполнений), Logical reads (число логических операций чтения), Logical writes (число логических операций записи), Memory consumption (потребление памяти), Physical reads (число физических операций чтения), CLR time (время среды CLR), Degree of parallelism (DOP) (степень параллелизма) и Row count (число строк) Average (среднее), Maximum (максимум), Minimum (минимум), Standard Deviation (стандартное отклонение), Total (всего)
SQL Server 2017 (14.x); CPU time (время ЦП), Duration (длительность), Execution count (число выполнений), Logical reads (число логических операций чтения), Logical writes (число логических операций записи), Memory consumption (потребление памяти), Physical reads (число физических операций чтения), CLR time (время среды CLR), Degree of parallelism (DOP) (степень параллелизма), Row count (число строк), Log memory (память, занимаемая журналом), TempDB memory (память, занимаемая базой данных TempDB) и Wait times (время ожидания) Average (среднее), Maximum (максимум), Minimum (минимум), Standard Deviation (стандартное отклонение), Total (всего)

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

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

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

SQL Server Представление Management Studio Сценарий
Регрессированные запросы Выявите запросы, метрики выполнения для которых недавно регрессировали (т. е. стали хуже).
Используйте это представление для сопоставления наблюдаемых проблем производительности в приложении с фактическими запросами, которые необходимо улучшить или исправить.
Общее потребление ресурсов Анализируйте общее потребление ресурсов базы данных для любой из метрик выполнения.
Используйте это представление для определения шаблонов ресурсов (дневная и ночная рабочие нагрузки) и оптимизации общего потребления для базы данных.
Основные запросы, потребляющие ресурсы Выберите интересующую метрику выполнения и определите запросы, которые имели максимальные значения в указанном промежутке времени.
Используйте это представление, чтобы сосредоточиться на наиболее важных запросах, которые оказывают самое большое воздействие на потребление ресурсов базы данных.
Запросы с принудительными планами Здесь приводятся планы, которые были принудительно выполнены ранее с помощью Query Store.
В этом представлении можно быстро получить доступ ко всем текущим принудительным планам.
Запросы с высокой вариативностью Анализ запросов с высокой вариативностью выполнения с учетом всех доступных параметров, таких как длительность, время ЦП, ввод-вывод данных и использование памяти, в соответствующем временном интервале.
Используйте это представление для выявления запросов с сильно варьируемой производительностью, которые могут влиять на работу пользователей в приложениях.
Статистика ожидания запросов Анализируйте категории ожидания, наиболее активные в базе данных, и определите, какие запросы вносят наибольший вклад в выбранную категорию ожидания.
Используйте это представление, чтобы проанализировать статистику ожидания и определить запросы, которые могут влиять на работу пользователей в приложениях.

Область применения: начиная с SQL Server Management Studio версии 18.0 и SQL Server 2017 (14.x);.
Отслеживаемые запросы Отслеживайте выполнение наиболее важных запросов в реальном времени. Как правило, эти представления используются, когда имеются запросы с принудительными планами и требуется убедиться в стабильной производительности запросов.

Совет

Подробное описание того, как использовать Среда Management Studio для выявления запросов с самым большим потреблением ресурсов и исправления тех, чьи метрики ухудшились из-за изменения плана, см. в блогах по хранилищу запросов Azure.

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

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

    План принудительного использования хранилища запросов

    Примечание

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

    Фигурная Значение
    Circle Запрос выполнен, то есть обычное выполнение успешно завершено.
    Square Запрос отменен, то есть клиент прервал выполнение.
    Triangle Сбой, то есть выполнение прервано с исключением.

    Кроме того, размер фигуры отражает количество выполнений запроса за указанный интервал времени. Чем больше это количество, тем больше размер фигуры.

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

    План отображения хранилища запросов

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

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

Совет

В База данных SQL Azure рекомендуется использовать функцию Указания хранилища запросов (предварительная версия) для принудительного выполнения указания запросов к запросам без изменения кода. Дополнительные сведения и примеры см. в разделе Указания хранилища запросов (предварительная версия).

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

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

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Разница между actual_state_desc и desired_state_desc показывает, что произошло автоматическое изменение режима работы. Самое частое изменение — автоматическое переключение хранилища запросов в режим "только чтение". В исключительно редких случаях хранилище запросов может оказаться в состоянии ошибки из-за внутренних ошибок.

Если фактическое состояние является режимом только чтения, используйте столбец readonly_reason для определения основной причины. Скорее всего, вы обнаружите, что хранилище запросов перешло в режим "только чтение" из-за превышения квоты на размер. В этом случае в столбце readonly_reason будет значение 65536. Другие причины см. в разделе sys.database_query_store_options (Transact-SQL).

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

  • Увеличение максимального размера хранилища с помощью параметра MAX_STORAGE_SIZE_MB инструкции ALTER DATABASE.

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

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
    

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

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Выполните следующие упреждающие действия.

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

Состояние ошибки

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

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Если проблема сохраняется, это означает повреждение данных в хранилище запросов, сохраненных на диске.

В версии SQL Server 2017 (14.x); и выше хранилище запросов можно восстановить, выполнив хранимую процедуру sys.sp_query_store_consistency_check в соответствующей базе данных. Прежде чем пытаться выполнять операцию восстановления, необходимо отключить хранилище запросов. В версии SQL Server 2016 (13.x); необходимо очистить данные в хранилище запросов, как показано ниже.

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

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

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

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

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

Выявление новых запросов в рабочей нагрузке.

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

Примечание. Это режим записи по умолчанию в версиях SQL Server 2016 (13.x); и SQL Server 2017 (14.x);.
Автоматически Сосредоточьте внимание на важных и действенных запросах. Примерами могут служить запросы, которые выполняются регулярно или потребляют ресурсы в значительных объемах.

Примечание. Начиная с версии SQL Server 2019 (15.x) это режим записи по умолчанию.
None Вы уже записали набор запросов, который хотите отслеживать в среде выполнения, и хотите исключить отвлекающие факторы, которые могут быть внесены другими запросами.

Значение None подходит для тестовых сред и сред тестирования производительности.

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

Значение None следует использовать с осторожностью, поскольку можно упустить возможность отслеживания и оптимизации важных новых запросов. Старайтесь не использовать None, если этого не требуется в конкретном сценарии.
Custom В версии SQL Server 2019 (15.x) реализован режим Custom с использованием команды ALTER DATABASE SET QUERY_STORE. Если этот параметр включен, для нового параметра политики записи хранилища запросов доступны дополнительные конфигурации хранилища запросов, что позволяет тонко настраивать сбор данных на конкретном сервере.

Новые пользовательские параметры определяют действия, которые выполняются при достижении внутреннего порогового значения времени для политики записи. Это временная граница, в пределах которой происходит оценка настраиваемых условий и, если какие-либо из них соблюдаются, разрешается запись запроса в хранилище запросов. Дополнительные сведения см. в разделе Параметры ALTER DATABASE SET (Transact-SQL).

Примечание

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

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

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

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

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

Старайтесь не использовать запросы без параметров

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

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

Следуйте приведенным ниже рекомендациям.

  • Параметризуйте запросы везде, где это возможно. Например, заключайте запросы в хранимую процедуру или sp_executesql. См. дополнительные сведения о параметрах и повторном использовании планов выполнения.
  • Используйте параметр Оптимизировать для нерегламентированной рабочей нагрузки, если рабочая нагрузка содержит много нерегламентированных пакетов для однократного использования с разными планами запроса.
    • Сравните число уникальных значений query_hash с общим числом записей в sys.query_store_query. Если их соотношение близко к 1, ваша нерегламентированная рабочая нагрузка создает разные запросы.
  • Применяйте принудительную параметризацию для базы данных или подмножества запросов, если количество разных планов запроса невелико.
    • Используйте структуру плана, чтобы применить принудительную параметризацию только в выбранном запросе.
    • Настройте принудительную параметризацию с помощью параметра базы данных Параметризация, если в рабочей нагрузке существует небольшое число разных планов запроса. Примером является ситуация, когда отношение числа разных query_hash к общему числу записей в sys.query_store_query намного меньше 1.
  • Установите параметр QUERY_CAPTURE_MODE в значение AUTO, чтобы динамические запросы с небольшим потреблением ресурсов отфильтровывались автоматически.

Старайтесь не использовать шаблоны DROP и CREATE для содержащих объектов

Хранилище запросов связывает запись запроса с содержащим объектом, например хранимой процедурой, функцией или триггером. При повторном создании содержащего объекта создается новая запись запроса для того же текста запроса. Это препятствует отслеживанию статистики производительности для этого запроса с течением времени и использованию механизма принудительного применения планов. Чтобы избежать такой ситуации, используйте процесс ALTER <object> для изменения определения содержащего объекта везде, где это возможно.

Регулярно проверяйте состояние принудительных планов

Форсирование плана — это удобный механизм, позволяющий исправлять производительность важных запросов и делать их более предсказуемыми. Как и в случае указаний планов и структур планов, не гарантируется, что это будет использовано в будущих выполнениях. Обычно, когда схема базы данных изменяется так, что объекты, упоминаемые в плане выполнения, изменяются или удаляются, принудительное выполнение плана начинает завершаться сбоем. В этом случае SQL Server возвращается к перекомпиляции запроса, а фактическая причина сбоя принудительного выполнения отображается в sys.query_store_plan. Следующий запрос возвращает информацию о принудительно выполненных планах.

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Полный список причин см. в статье sys.query_store_plan. Кроме того, можно использовать XEvent query_store_plan_forcing_failed для отслеживания и устранения неполадок с принудительным выполнением планов.

Совет

В База данных SQL Azure рекомендуется использовать функцию Указания хранилища запросов (предварительная версия) для принудительного выполнения указания запросов к запросам без изменения кода. Дополнительные сведения и примеры см. в разделе Указания хранилища запросов (предварительная версия).

Избегайте переименования баз данных для запросов с принудительными планами

Планы выполнения ссылаются на объекты по трехкомпонентным именам, например database.schema.object.

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

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

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

  • Флаг трассировки 7745 препятствует поведению по умолчанию, при котором хранилище запросов записывает данные на диск до того, как SQL Server сможет завершить работу. Это означает, что будут потеряны все собранные данные хранилища запросов, которые еще не сохранены на диске, за период времени, определенный в DATA_FLUSH_INTERVAL_SECONDS.
  • Флаг трассировки 7752 включает асинхронную загрузку для хранилища запросов. Это позволяет восстановить базу данных и выполнять запросы раньше, чем будет полностью восстановлено хранилище запросов. По умолчанию загрузка хранилища запросов выполняется в синхронном режиме. Этот вариант не позволяет выполнять запросы до полного восстановления хранилища запросов, но зато предотвращает потерю запросов при сборе данных.

Примечание

Начиная с версии SQL Server 2019 (15.x) это поведение управляется подсистемой и флаг трассировки 7752 не оказывает влияния.

Важно!

Если вы используете хранилище запросов для JIT-анализа рабочих нагрузок в SQL Server 2016 (13.x);, запланируйте установку улучшений масштабируемости производительности в накопительном обновлении 2 (CU2) SQL Server 2016 (13.x); SP2 (статья базы знаний 4340759) как можно скорее. Без этих улучшений, если база данных испытывает значительные рабочие нагрузки, может возникнуть конкуренция циклической блокировки и производительность сервера может снизиться. В частности, вы можете столкнуться с интенсивной конкуренцией циклической блокировки QUERY_STORE_ASYNC_PERSIST или циклической блокировки SPL_QUERY_STORE_STATS_COOKIE_CACHE. После применения этого улучшения хранилище запросов больше не будет вызывать конфликты циклической блокировки.

Важно!

Если вы используете хранилище запросов для JIT-анализа рабочих нагрузок в SQL Server (SQL Server 2016 (13.x); через SQL Server 2017 (14.x);), запланируйте установку улучшений масштабируемости производительности в SQL Server 2016 (13.x); с пакетом обновления 2 (SP2) и накопительным пакетом обновления 15 (CU15), SQL Server 2017 (14.x); с накопительным пакетом обновления 23 (CU23) и SQL Server 2019 (15.x) с накопительным пакетом обновления 9 (CU9) как можно скорее. Без этого улучшения, если база данных испытывает значительные нерегламентированные рабочие нагрузки, хранилище запросов может потреблять большой объем памяти, а производительность сервера может снизиться. После применения этого улучшения хранилище запросов накладывает внутренние ограничения на объем памяти, которую могут использовать различные компоненты, и может автоматически изменить режим работы на "только для чтения" до тех пор, пока в Компонент Database Engineне будет возвращен достаточный объем памяти. Обратите внимание, что ограничения внутренней памяти хранилища запросов не документированы, так как они могут быть изменены.

Использование хранилища запросов в активной георепликации Базы данных SQL Azure

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

Избегайте несоответствия уровней с георепликацией База данных SQL Azure. База данных-получатель должна иметь такой же или практически такой же объем вычислительных ресурсов, что и база данных-источник, и находиться на том же уровне службы, что и база данных-источник. Взгляните на тип ожидания HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO в sys.dm_db_wait_stats, который указывает на регулирование скорости журнала транзакций на первичной реплике из-за задержки на вторичной реплике.

Дополнительные сведения об оценке и настройке размера Базы данных-получателя SQL Azure с активной георепликацией см. в разделе Настройка базы данных-получателя.

См. также раздел