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

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

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

• Выявление и фиксация запросов с регрессией в выборе плана

• Выявить и настроить запросы, использующие больше всего ресурсов.

• Тестирование А/Б

• Поддержание стабильной производительности во время обновления до новой версии SQL Server

• Определение и улучшение нерегламентированных рабочих нагрузок

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

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

• Сведения о работе с хранилищем запросов в базе данных SQL Azure см. в разделе Работа с хранилищем запросов в базе данных SQL Azure.

Выявление и фиксация запросов с регрессией в выборе плана

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

С помощью хранилища запросов можно быстро:

• определить все запросы, метрики выполнения которых ухудшились за соответствующий период (последний час, день, неделю, и т. д.). Используйте регрессивные запросы в SQL Server Management Studio, чтобы ускорить анализ.

• Среди регрессивных запросов легко найти те, которые включают несколько планов и производительность которых упала в результате неправильного выбора плана. Для визуализации всех планов, выявления запроса с регрессией и изменений в производительности запросов с течением времени используйте панель Plan Summary (Сводка планов) в разделе Regressed Queries (Регрессивные запросы).

• Следует принудительно вернуться к предыдущему плану в журнале, если тот оказался лучше. Чтобы принудительного выбрать план для запроса, нажмите кнопку Принудительно использовать план в разделе Регрессированные запросы.

Подробное описание сценария см. в блоге Хранилище запросов: "черный ящик" вашей базы данных .

Выявить и настроить запросы, использующие больше всего ресурсов.

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

Простейший способ начать исследование — это открыть раздел Основные запросы, потребляющие ресурсы в Management Studio. Пользовательский интерфейс разделен на три области: гистограмму, показывающую, какие запросы задействуют больше всего ресурсов (слева), сводку планов для выбранного запроса (справа) и визуальный план для выбранного запроса (внизу). Выберите пункт Настройка, чтобы задать число запросов для анализа и указать период времени. Кроме того, можно выбрать различные аспекты использования ресурсов (длительность, ЦПУ, память, операции ввода-вывода, число выполнений) и базовый уровень (среднее, максимум, минимум, итог, стандартное отклонение).

Сводка планов справа позволяет анализировать историю выполнения и узнавать о различных планах и статистике времени их выполнения. Нижняя область позволяет изучать различные планы или визуально их сравнивать при параллельной обработке (используйте кнопку Compare (Сравнить)).

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

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

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

3. Убедитесь в том, что статистические данные актуальны для базовых таблиц, которые использует запрос.

4. Убедитесь, что индексы, которые использует запрос, дефрагментированы.

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

Тестирование А/Б

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

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

• Развертывание новой версии приложения.

• Установка на сервер нового оборудования.

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

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

• Добавление временного системного управления версиями в таблицы, которые часто изменяются приложениями OLTP.

В любом из этих случаев применяется следующий рабочий процесс:

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

2. Примените изменение приложения в управляемый момент времени.

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

4. Сравните результаты шагов 1 и 3.

   1. Откройте раздел Overall Database Consumption (Общее использование базы данных), чтобы определить влияние на всю базу данных.

   2. Откройте раздел Основные запросы, потребляющие ресурсы (или выполните собственный анализ с помощью Transact-SQL), чтобы проверить, как изменение повлияло на большинство важных запросов.

5. Сохраните изменение либо, если новый уровень производительности неприемлем, выполните откат.

На следующей иллюстрации показан анализ хранилища запросов (шаг 4) при создании отсутствующего индекса. Откройте раздел Top Resource Consuming Queries (Запросы, потребляющие больше всего ресурсов) и перейдите на страницу Plan Summary (Сводка планов), чтобы получить представление запроса, на которое должно повлиять создание индекса:

Кроме того, вы можете сравнить планы до и после создания индекса, обработав их параллельно. (Параметр "Сравнение планов для выбранного запроса в отдельном окне" в панели, отмеченный красным квадратом.)

План до создания индекса (plan_id = 1, выше) имеет подсказку об отсутствии индекса и позволяет убедиться в том, что больше всего ресурсов задействовал содержащийся в опросе оператор Clustered Index Scan (красный прямоугольник).

План после создания отсутствующего индекса (plan_id = 15, ниже) теперь включает оператор Index Seek (Nonclustered), уменьшающий общую ресурсоемкость запроса и повышает его производительность (зеленый прямоугольник).

Согласно результатам анализа индекс можно сохранить, поскольку производительность запроса повысилась.

Поддержание стабильной производительности во время обновления до новой версии SQL Server

До SQL Server 2014 (12.x) пользователи подвергались риску снижения производительности при обновлении до последней версии платформы. Причина в том, что последняя версия оптимизатора запросов становилась доступной сразу после установки новых битов.

Начиная с SQL Server 2014 (12.x), все изменения в оптимизаторе запросов привязаны к последнему уровню совместимости базы данных, поэтому планы изменяются не в момент обновления, а когда пользователь изменяет COMPATIBILITY_LEVEL на последнюю версию. В сочетании с хранилищем запросов эта возможность обеспечивает высокий уровень контроля над производительностью запросов в процессе обновления. Рекомендуемый рабочий процесс обновления показан на следующем рисунке:

1. Обновите SQL Server, не изменяя уровень совместимости базы данных. При этом не предоставляются последние изменения оптимизатора запросов, но появляется доступ к новым функциям SQL Server, включая хранилище запросов.

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

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

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

5. Используйте хранилище запросов для анализа и устранения проблемы регрессии: обычно новые улучшения оптимизатора запросов должны улучшать планы. Тем не менее хранилище запросов позволяет легко установить регрессию при выборе плана и устранить ее с помощью механизма принудительного применения планов. Начиная с версии SQL Server 2017 (14.x) при использовании функции Автоматическое исправление плана это действие становится автоматическим.

   a. При обнаружении регрессии принудительно перейдите к предыдущему, более эффективному плану в хранилище запросов.

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

Определение и улучшение нерегламентированных рабочих нагрузок

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

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

С помощью метрики Счетчик выполнений можно проанализировать, являются ли нерегламентированными запросы, потребляющие больше всего ресурсов (для этого хранилище запросов необходимо запустить с оператором QUERY_CAPTURE_MODE = ALL). Из приведенной выше схемы видно, что 90 % ваших запросов, потребляющих больше всего ресурсов , выполняется только один раз.

Кроме того, вы можете выполнить сценарий Transact-SQL, чтобы получить общее число текстов запросов, запросов и планов в системе и определить, насколько они отличаются, сравнив значения query_hash и query_plan_hash.

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Вот один из результатов, которые можно получить при рабочей нагрузке с нерегламентированными запросами:

Результат запроса показывает, что, несмотря на большое число запросов и планов в хранилище запросов, фактически их значения query_hash и query_plan_hash не отличаются. Соотношение между уникальными текстами запросов и уникальными хэшами запросов, намного превышающими 1, указывает на то, что рабочая нагрузка является хорошим кандидатом для параметризации, так как единственная разница между запросами — это константа-литерал (параметр), указанная в тексте запроса.

Как правило, это происходит, если приложение создает запросы (а не вызывает хранимые процедуры или параметризованные запросы) либо зависит от платформ объектно-реляционного сопоставления, которые формируют запросы по умолчанию.

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

В случае с отдельными шаблонами запросов требуется создание структуры плана:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Решения с использованием структуры плана работает точнее, но требует больше работы.

Если все запросы (или большинство из них) являются кандидатами на автоматическую параметризацию, рассмотрите возможность настройки PARAMETERIZATION = FORCED для всей базы данных. См. рекомендации по использованию принудительной параметризации.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

После выполнения любого из этих шагов картина рабочей нагрузки в разделе Top Resource Consuming Queries (Запросы, потребляющие больше всего ресурсов) полностью изменится.

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

В этом случае рекомендуется включить параметр сервера Оптимизировать для нерегламентированной рабочей нагрузки, чтобы предотвратить потерю кэш-памяти при выполнении запросов, которые, скорее всего, не будут выполняться снова. Чтобы запретить запись этих запросов в хранилище запросов, задайте для параметра QUERY_CAPTURE_MODE значение AUTO.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Следующие шаги

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