MSSQL: рисуем метрики из Query Store на листинге процедуры

В моей предыдущей статье я отображал метрики из записанного SQL profiler trace на листинг stored процедуры. Это идеально подходит для тестовых окружений, но в production надо быть осторожным, и запись «частых» событий могут увеличивать CPU сервера и замедлять его работу.

@speshuric предложил использовать данные из Query Store. Там, правда, нет номеров строк. Но можно выкрутиться, так как есть смещения и можно посчитать количество переводов строки до смещения. Итак, сказано — сделано!

Подготовка

Здесь мы работаем непосредственно на сервере. Поэтому я писал код на PowerShell, потому что он гарантированно есть на сервере. В репозитории https://github.com/tzimie/sqlpig скачиваем файл querypig.ps1.

Запуск

Запускаем скрипт на сервере:

powershell .\querypig.ps1 server database procedure [flags]

• server — имя сервера. Подразумевается integrated seciurity

• database — имя базы данных

• procedure — имя процедуры, можно указывать схему (подходит все, что понимает встроенная функция object_id)

• флаги (не обязательно) — размер табуляции (текст преобразуется в html и надо знать, во сколько пробелов превращать табуляцию, по умолчанию 4) и флаг P говорит о том, что надо сохранять планы. Например,

  • 2 — табуляция два пробела, планы не собираются

  • P3, 3P — три пробела и планы собираются

  • P — планы собираются (4 проблема по умолчанию)

Результат

Программа создаст html файл c тем же именем, что и имя процедуры. Очень наивный код делает нечто, что можно назвать подсветкой синтаксиса. Я не хотел тратить на это время, поэтому не поддерживаются вложенные комментарии и прочие более менее сложные случаи.

Строки, к которым нашлись ассоциированные метрики из Query Store показывают tooltip со всей информацией:

Если заказана генерация планов, то создается каталог с именем, совпадающим с именем процедуры, и в нем создаются файлы вида plan<номерстроки>.sqlplan:

соответствующие номерам строк листинга. Вы можете посмотреть их с помощью SSMS.

Когда лучше использовать первый способ, а когда — второй?

На production лучше использовать querypig. Влияния на сервер нет, но есть и минусы — вы не можете спрофилировать определенное выполнение, и видите только агрегированные данные. Кроме того, query store ограничен по объему, и содержит не все запросы. Поэтому информация может быть неполной и ‘не биться’ к 100%, к общему числу запросов и друг с другом (записи в Query store могут вытесняться и устаревать)

Использование SQL profiler с sqlpig дает гарантированное и полное покрытие кода, и вы можете контролировать параметры запуска и время сбора данных. Но на production событие SP:StmtComplete может слишком часто возникать, и его фильтрация может приводить к повышенному CPU и к замедлению сервера.