В моей предыдущей статье я отображал метрики из записанного 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 и к замедлению сервера.