Ссылка на статью на сайте автора: ссылка

В SQL Server 2016 появилась ещё одна новая интересная функциональность. Это Query Store. Она может помочь вам в устранении неполадок производительности запросов.

Примечание: все описанное ниже работает в SQL Server 2016 CTP 2.1

Без возможности увидеть всё что изменилось в планах запросов сложно понять почему всё вдруг стало плохо (я имею в виду производительность запросов J). Решение этой проблемы может занять часы, а иногда дни.Query Store похож на черный ящик в самолете. Он(а) записывает детализированную историческую информацию о всех запросах. Собранные данные разделяются по временным окнам, что позволяет понять, как менялась производительность в течении времени.

Итак, давайте включим Query Store. Это можно сделать как через интерфейс SSMS,

так и через команду T-SQL:

ALTER DATABASE <database name> SET QUERY_STORE = ON

Примечание 2: Нельзя использовать Query Storе для баз данных master и tempdb.

Примечание 3: Для новых баз данных Query Store не включается автоматически.

Все настройки, доступные в SSMS, можно поменять через T-SQL с помощью команды ALTER DATABASE. Например,

ALTER DATABASE AdventureWorks2014
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
)

Рекомендую обратить внимание на опции:

• Max Size – ограничение на хранилище, доступное для Query Store в текущей базе данных. Значение по умолчанию – 100 MB. Если этого объема не хватит (например, если в вашей базе данных генерируется много запросов или планов или вы храните историю этих запросов длительное время), то Query Store перейдет в режим Read Only, т.е. не будет собирать новые данные.
• Statistics Collection Interval – определяет уровень гранулярности для сбора статистики. Значение по умолчанию – 1 час. Вы можете уменьшить этот интервал, но не забудьте, что это повлияет на размер данных в Query Store

Текущие значения опций можно посмотреть в системном представлении sys.database_query_store_options

Сразу после активации Query Store начинает собирать информацию. Query Store содержит два хранилища:

• plan store для информации о планах исполнения (число уникальных планов для запроса, которые могут храниться в plan store ограничивается опцией max_plans_per_query)
• runtime stats store для информации о статистике исполнения

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

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id

Давайте рассмотрим типичные сценарии, в которых можно использовать Query Store:

• получить top n запросов (по времени исполнения, потреблению памяти и т.п.) за последние x часов
• запросы, которые потеряли в производительности с течением времени
• сколько раз запрос был выполнен в некоторый интервал времени
• аудит истории планов для данного запроса.
• анализ использования ресурсов (CPU, I/O, память) для конкретной базы данных
• и т.д.

Сделать эти варианты анализа можно с помощью запросов к sys.query_store_plan и sys.query_store_query. Но есть ещё одна возможность. После включения Query Store в SSMS для этой базы данных будут доступны четыре шаблона.

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

С помощью этих шаблонов или запросов к Query Store вы сможете найти план запроса, который работал лучше чем текущий и при необходимости применить его к запросу с помощью хранимой процедуры sys.sp_query_store_force_plan. Это может дать вам время провести более детальный анализ ситуации и исправить её.

Ну, и напоследок. Если необходимо очистить хранилище, то используйте командуALTER DATABASE <database name> SET QUERY_STORE CLEAR

Для более детального знакомства с Query Store рекомендую почитать и посмотреть:

•  Monitoring Performance By Using the Query Store
  
• Query Store: A flight data recorder for your database
• Query Store in SQL Server 2016 

*** *** *** *** *** *** *** *** *** *** *** ***
Ссылка на наш канал YouTube

Обсудить тему можно в комментариях или вконтакте.

Не забывайте смотреть рубрику «Грядущие события и мероприятия»