SQL, Анализ данных, Саморазвитие

Поиск наименее ресурсоёмких запросов с помощью Query Store

Время прочтения: 3 мин.

Для написания производительного запроса, первым делом, мы всегда смотрим его план. Но что делать, если план запроса почему-то изменился? Как определить: сколько ресурсов он потребляет? И как провести сравнительный анализ запросов?

Разработчики MS SQL Server 2016 предоставляют такую возможность с помощью инструмента Query Store. Query Store записывает данные о выполняемых запросах в системное хранилище, содержащее информацию о планах и статистике запросов. Для активации Query Store можно воспользоваться как настройками свойств БД, так и следующим запросом:

ALTER base_name SET QUERY_STORE = ON, где base_name - это имя базы данных

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

Особое внимание стоит обратить на параметр max_size, который определяет ограничение на размер хранилища запросов (по умолчанию 100 Мб). Если размер Query Store превысит max_size, то его режим работы перейдёт в состояние read only, то есть новые данные перестанут поступать в хранилище. Текущие значения параметров можно извлечь из представления sys. database_query_store_options.

Всю необходимую информацию, содержащуюся в Query Store, можно найти с помощью следующего запроса:

select *
from sys.query_store_plan p 
join sys.query_store_query q on p.query_id = q.query_id
join sys.query_store_query_text qt on q.query_text_id = qt.query_text_id

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

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

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

N.B. Инструмент не работает с базами tempdb и master.

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

Советуем почитать