Нормализация SQL profiler трейса для группировки

Если вы разбираетесь «почему тормозит база» и у вас есть трейс, созданный MS SQL profiler, то что вы делаете первым делом? Правильно, сохраняете его в таблицу, чтобы поразбираться с ним с помощью родного SQL, а не в GUI.

Очень хотелось бы сделать group by TextData, но увы — так не получится из-за разных параметров у процедур и кверей. А выразительных способностей SQL не хватет, чтобы эффективно ‘нормализовать’ трейс.

Но ведь можно скрестить ежа и ужа, SQL и Python, и решить задачу в несколько строк! Полезные скрипты ниже

Итак, какие преобразования нам хотелось бы сделать?

Общую нормализацию (много пробелов подряд, табуляции и переводы строк).

Далее, заменим числа (как целые, так и hex) на #. А все строки заменим на ‘~’, например:

select 1,'alpha',0x478ddaaee,X,N'str' from TAB   -- превратится в  select #,'~',#,X,N'~' from TAB

Это не решает проблему списков разной длины:

select * from TAB where id in (1,2,3) select * from TAB where id in (5,6,7,8) -- получится select * from TAB where id in (#,#,#) select * from TAB where id in (#,#,#,#)

Поэтому заменим списки чисел на <nlist>, а строк на <slist>, но только если до списка есть хоть одна открывающая скобка, иначе это список параметров процедуры:

exec prc 1,2,3,'alpha','beta' select * from TAB where id in (5,6,7,8) select * from STAB where names in ('alpha','beta') -- получится exec prc #,#,#,'~','~' select * from TAB where id in (<nlist>) select * from STAB where names in (<slist>)

Обратите внимание, что мы не приводим параметры процедуры к списку.

Далее, для sp_executesql и sql_cursorprepexec мы ‘выкусываем’ сам оператор, который потом обрабатывается по общим правилам:

exec sp_executesql N'SELECT * FROM TAB where CAT=N''X'' and id=@p__linq__0'    ,N'@p__linq__0 int',@p__linq__0=725 -- получится    SELECT * FROM TAB WHERE CAT=N'~' and id=@p__linq__0

Наконец, часто встречаются временные таблицы с именами, сгенеренными автоматически:

select * INTO [#sells_a5250e98_2ec7_495a_abe2_e314d0a9b5e6] from X drop table [#f42350e3_4aa3_1234_25e4_e314d0adf3e2] -- получится select * INTO [#sells...] from X drop table [#G]

В первом случае имя усекается по первому подчеркиванию, во втором, когда все имя — GUID, остается #G

А теперь сами скрипты

Вначале подготовка, должно быть включено:

EXECUTE sp_configure 'external scripts enabled', 1; GO RECONFIGURE; GO

Далее создадим табличку в базе, где мы сохраняем трейсы. Назовем ее T. Список колонок вы можете пополнять по своему вкусу.

create table T (rowNumber int, TextData varchar(max),    CPU int, Reads int, Writes int, Duration int)

А теперь главное. Обратите внимание, что в коде Python все одинарные кавычки удвоены, так как вы передаете это из SQL:

set nocount on  truncate table T insert into T EXECUTE sp_execute_external_script @language = N'Python'     , @script = N' import re  def norm(s):   # initial cleanup   s = s.replace("\n", " ").replace("\r", " ").replace("\t", " ") # eols & tabs   s = s.replace("  ", " ").strip()   s = re.sub("\s+"," ",s)+" " # get rid of multiple spaces, last space for parsing numbers at the end    # open sql_executesql   if (s[0:19].casefold() == "exec sp_executesql "):      s = s[21:]     s = re.sub("(.*?[^''])''.*\Z","\\1",s) # leftmost quote which is not double quote    # sp_cursorprepexec   prep = re.compile(".*\Wexec sp_cursorprepexec\W", re.IGNORECASE)   if prep.match(s):     s = s.rsplit(",N''",1)[1] # extract statement after N     s = re.sub("(.*?[^''])''.*\Z","\\1",s) # leftmost quote which is not double quote     s = "sp_cursorprepexec " + s.replace("''''","''") + " "    s = re.sub("''''", "",s) # replace double single quotes with nothing   s = re.sub("(\\W)#(\w{36,36})(\\W)", "\\1#G\\3", s) # replace #<guid name> with #G   s = re.sub("(\\W)(#[^\W_]+)(\w+)(\\W)", "\\1\\2...\\4", s) # replace unique temp table names #tab_12345 with #tab...      # replace any number with # loop because , is eaten by last \W so some values are skipped   while True:     new = re.sub("(\\W)(\\d+)(\\W)", "\\1#\\3", s)      if new == s: break     s = new   s = re.sub("-#", "#", s) # negative numbers    # hex numbers   while True:     new = re.sub("(\\W)(0x[0-9A-Fa-f]+)(\\W)", "\\1#\\3", s)      if new == s: break     s = new    s = re.sub("(\'')(.*?)(\'')", "''~''", s) # replace any string with ''~''    # remove spaces around , so lists can be always collapsed   s = re.sub("\s,", ",", s)   s = re.sub(",\s", ",", s)    # collapse numeric lists, there must be at least one ( before the list, otherwise these are parameters   while True:     new = re.sub("(\(.*?)#,#", "\\1<nlist>", s)     if new == s: break     s = new   while True:     new = re.sub("<nlist>,<nlist>", "<nlist>", s)     if new == s: break     s = new   s = re.sub("<nlist>,#", "<nlist>", s)    # collapse string lists   while True:     new = re.sub("(\(.*?)''~'',''~''", "\\1<slist>", s)     if new == s: break     s = new   while True:     new = re.sub("<slist>,<slist>", "<slist>", s)     if new == s: break     s = new   s = re.sub("<slist>,''~''", "<slist>", s)    return s  OutputDataSet = InputDataSet n = 0 for r in OutputDataSet["RowNumber"]:   s = OutputDataSet["TextData"][n]   OutputDataSet["TextData"][n] = norm(s)   n += 1 ',  @input_data_1 = N'SELECT RowNumber,convert(nvarchar(max),TextData) as TextData,    CPU,Reads,Writes,Duration/1000 as Duration FROM trc where textData is not null' 

Особое внимание на последние две строчки: замените trc на название вашей таблицы с трейсом, и не забудьте добавить сюда еще колонки, если добавляли из в таблицу T. Duration приводится к миллисекундам из микросекунд.

Сама группировка тривиальна:

declare @totalCPU float, @totalReads float, @totalWrites float, @totalDuration float  select @totalCPU=sum(CPU), @totalReads=sum(Reads), @totalWrites=sum(Writes), @totalDuration=sum(Duration) from T  select count(*) as cnt,    sum(CPU) as CPU,sum(Reads) as Reads, sum(Writes) as Writes, sum(Duration) as Duration,     sum(CPU)/(@totalCPU+0.001) as PctCPU, sum(Reads)/(@totalReads+0.001) as PctReads,    sum(Writes)/(@totalWrites+0.001) as PctWrites,sum(Duration)/(@totalDuration+0.001) as PctDuration,     TextData from T     group by TextData order by 1 desc

Выводим количество операторов и суммы CPU, Reads, Writes, Duration как есть и в процентах к полному трейсу. В конце ORDER BY по вкусу.

Скорость обработки составляет около 120 строк в секунду, так что на трейс из миллиона записей у вас уйдет около трех часов. Наверное, это можно ускорить — но зачем? Пока обрабатывается трейс, вы сможете погулять или посмотреть фильм не коря себя тем, что вы якобы ничего не делаете.

И еще полезность

Мне приходилось исследовать зависимость скорости выполнения процедуры от параметров. Например, в трейсе идут записи:

exec MyReport @from='2022-01-01',@to='2022-01-20',@flag='all' exec MyReport @from='2020-01-01',@to='2022-01-20',@flag='some' exec MyReport @from='2022-01-20',@to='2022-01-20',@flag='all'

Некоторые вызовы были куда медленнее чем обычно, и я предположил, что дело в интервале времен, которые обрабатывает отчет. Поможет функция:

create function [dbo].[getpar] (@par varchar(32), @t varchar(max)) returns varchar(8000) as   begin   declare @i int   set @i = charindex('@'+@par,@t)   if @i < 0 return ''   set @t=substring(@t,@i+len(@par)+2,8000)   if left(@t,1)='N' set @t=substring(@t,2,8000)   if left(@t,1)='''' set @t=substring(@t,2,8000)   set @i = charindex(',@',@t)   if @i > 0 set @t=substring(@t,1,@i-1)   if left(reverse(@t),1)='''' set @t=substring(@t,1,len(@t)-1)   return @t   end

Теперь вы можете проанализировать вызовы так:

select 1+datediff(dd,          convert(datetime,dbo.getpar('from',TextData)),          convert(datetime,dbo.getpar('to',TextData))) as days   , CPU,Reads,Writes,Duration/1000 as Duration   from MyTrace where TextData like 'exec%MyReport%'

Теперь можно проверить гипотезу, построив Excel X-Y scatter diagram (X-days, Y-Duration или CPU)