SQL Server 2016: Query Store

By | July 5, 2015

В 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 рекомендую почитать и посмотреть:

2 thoughts on “SQL Server 2016: Query Store

  1. Pingback: SQL Server 2016: Live Query Statistics | Korshikov's

  2. Pingback: SQL Server 2016. Знакомимся с новинками | Korshikov's

Comments are closed.