Написание SQL-запросов при всей кажущейся простоте – это настоящее искусство. Казалось бы, нужно просто забрать лежащие в базе данные. Но сложность состоит в том, чтобы запрос был производительным и ресурсоёмким. И как понять, насколько он ресурсоёмок по сравнению с остальными?
Для написания производительного запроса, первым делом, мы всегда смотрим его план. Но что делать, если план запроса почему-то изменился? Как определить: сколько ресурсов он потребляет? И как провести сравнительный анализ запросов?
Разработчики MS SQL Server 2016 предоставляют такую возможность с помощью инструмента Query Store. Query Store записывает данные о выполняемых запросах в системное хранилище, содержащее информацию о планах и статистике запросов. Для активации Query Store можно воспользоваться как настройками свойств БД, так и следующим запросом:
Параметры Query Store регулируются или через настройки базы данных, или через соответствующие изменениям запросы.
Особое внимание стоит обратить на параметр max_size, который определяет ограничение на размер хранилища запросов (по умолчанию 100 Мб). Если размер Query Store превысит max_size, то его режим работы перейдёт в состояние read only, то есть новые данные перестанут поступать в хранилище. Текущие значения параметров можно извлечь из представления sys. database_query_store_options.
Всю необходимую информацию, содержащуюся в Query Store, можно найти с помощью следующего запроса:
Однако, на мой взгляд, в большинстве задач будет удобнее воспользоваться предлагаемым графическим интерфейсом, благодаря которому гораздо проще администрировать запрос. Его можно найти в обозревателе объектов среди вкладок соответствующей базы данных с включенным хранилищем.
В работе мне понадобилось определить какие запросы являются наиболее ресурсоёмкими. Интерфейс хранилища запросов позволяет определить и самые длительные запросы, и самые ресурсоёмкие с точки зрения памяти, и те, на обработку которых процессор затратил наибольшее время.
Советую обратить особое внимание на метрику с физическими считываниями. Дело в том, что на данный момент, в большинстве систем скорость чтения с диска несопоставима со скоростью обработки в оперативной памяти, поэтому операции с диском – чтение и запись, вероятно, будут являться узким местом системы.И, наконец, инструмент позволяет подробно изучить планы любых запросов, зафиксированных хранилищем, что также является хорошим подспорьем в анализе производительности запросов.
N.B. Инструмент не работает с базами tempdb и master.В итоге, даже поверхностное знакомство с Query Store показывает, что это мощный и удобный инструмент для администрирования запросов и ресурсов базы данных, однако требующий тонкой настройки и внимательного отношения.