Раскраска листинга процедуры T-SQL значениями метрик04.04.2025 17:47

Сразу покажу, о чем идет речь, чтобы вы решили, нужно вам это или нет.

39e9cdb26aa46da32cae7478628dd793.png

К операторам Tsql процедуры мы дописываем количество выполнений (x), cpu в миллисекундах ©, duration в микросекундах (d), число чтений ®, записей (w), число записей @@rowcount (n)

Можно показать как абсолютные значения, так и проценты

Сбор данных

Запустите SQL profielr и выберите единственное событие:

  • SP: StmtCompleted

Выберите следующие колонки:

  • CPU

  • Duration

  • Reads

  • Writes

  • StartTime

  • EndTime

  • LineNumber

  • ObjectName

  • SPID

  • RowCounts

Иногда данные в RowCounts не собираются, если не указано TextData.

В фильтрах укажите ObjectName = имя процедуры. Имя процедуры указывается без схемы и скобок, например, для процедуры [dbo].[MyProc] укажите просто MyProc

Если вы профилируете процедуру на dev и знаете SPID, то укажите его. Для Production старайтесь ограничить объем информации единственным SPID, хотя для самой программы разметки это необязательно.

Меня спрашивали, почему я использую SQL profiler, а не Extended Events. Вы можете использовать и их. Для коротких ad hoc трейсов иногда проще и быстрее сделать трейс с помощью SQL profiler. На production, конечно, надо быть осторожным, чтобы не получить runaway trace.

После запуска трейса соберите достаточно данных и запишите их в таблицу.

Подготовка окружения

Вам понадобится Python. Скачайте пакет pygments Welcome! — Pygments:

pip install Pygments

У вас появилась команда pygmentize. Скачайте еще один скрипт из моего репозитория:

https://github.com/tzimie/sqlpig

Проект первоначально назывался SQLpyg (SQL + pygmentize), но потом превратился в SQLpig.

В самом начале скрипта sqlpig.py есть запрос, который вам понадобится: 

select ObjectName+'.sql:'+convert(varchar,LineNumber)+':'+
  convert(varchar,cnt)+':'+
  convert(varchar,CPU)+':'+
  convert(varchar,Duration)+':'+
  convert(varchar,Reads)+':'+
  convert(varchar,Writes)+':'+
  convert(varchar,isnull(RowCounts,0))
from (
  select ObjectName,LineNumber, count(*) as cnt, sum(CPU) as CPU,sum(Duration) as Duration,
    sum(Reads) as Reads, sum(Writes) as Writes, sum(RowCounts) as RowCounts
  from {MyTrace} where EventClass=45 and ObjectName is not null
  group by ObjectName,LineNumber) Q

Только замените {MyTrace} на имя таблицы, куда вы записали трейс. Выполните скрипт и его вывод запишите в текстовый файл (.log). Можно назвать этот файл также, как и процедуру, либо добавлять суффикс short, long для разных выполнений.

Наконец, нам понадобится исходный текст процедуры. В Management Studio сделайте Alter для процедуры и скопируйте полученный текст в файл имяпроцедуры.sql

Внимание: вам надо удалить заголовок:

132de79749a788737a91583cd5b77e3f.png

Первой должна стать первая значимая строка — ALTER или первая строка комментария.

Вы можете спросить -, а почему, собственно, скрипт не может сам прочитать данные и текст процедуры и трейс? Дело в том, что трейс делается на production, а на сервере часто ставить ничего нельзя. Поэтому тащим все на свой комп через copy/paste (если он включен)

Обработка

Запустим sqlpig:

412c4a1a1ad386348eefd4526d799314.png

Метрики из myproc.log накладываются на файл myproc.sql и выводятся в myproc.html. Можно указать разные метрики:

  • x — число выполнений, не самой процедуры, а оператора, если он в цикле, то число выполнений может быть куда больше, чем число выполнений процедуры

  • c — CPU в ms

  • d — duration в микросекундах (более точно, чем CPU)

  • r — Reads

  • w — Writes

  • n — число записей из колонки RowCounts (оно не всегда честное и заслуживает отдельного разговора)

  • XCDRWN — uppercase метрики — это те же метрики, но в процентах к полной сумме

Возможно указать как процентные, так и абсолютные метрики, например, xcCdD валидная комбинация.

Последний параметр — это имя log файла. К одной процедуре у вас могут быть собраны результаты многих прогонов, например,

c84e3abd4d266ac92474245c6830bcba.png

Возможные проблемы

Иногда точки с метриками оказываются смещенными относительно строк исходного кода. Я постепенно вычищу краевые случаи, пока можно исправить значение в исходном коде:

shift = 1 # edit this value to readjust position if needed

© Habrahabr.ru