Построение Flame Chart для MSSQL20.03.2025 22:46

create procedure flame
  @trname sysname, @spname sysname, @start int=0
as
  -- Trace must contain Sp:Start, Sp:Completed, StartTime, EndTime, Nestlevel, ObjectName
  -- 1 SPID should be recorded
  set nocount on
  declare @r int, @b int, @e int, @t datetime, @class int, @lev int, @obj varchar(255), @prevt datetime, 
    @stack varchar(max)='', @prevstack varchar(max)='', @sql varchar(1000)
  create table #trace (RowNumber int, EventClass int, StartTime datetime, EndTime datetime, NestLevel int, ObjectName nvarchar(255))
  set @sql = 'insert into #trace select RowNumber,EventClass,StartTime,EndTime,Nestlevel,ObjectName from '+@trname+' where EventClass in (42,43)'
  exec (@sql)
  select (select max(RowNumber) from #trace I where I.EventClass=42 and I.ObjectName=@spname and I.Nestlevel=1 and I.RowNumber=@start
  if @b is null begin print 'procedure: start not found' return end
  select @e=min(RowNumber) from #trace where EventClass=43 and ObjectName=@spname and RowNumber>@b and Nestlevel=1
  if @e is null begin print 'procedure: end not found' return end
  select 'Selected', @b as [Start], @e as [End]
  DECLARE my_cur CURSOR FOR SELECT RowNumber,EventClass,isnull(EndTime,StartTime),NestLevel,ObjectName 
    from #trace where RowNumber>=@b and RowNumber<=@e order by RowNumber
  OPEN my_cur
  FETCH NEXT FROM my_cur INTO @r, @class, @t, @lev, @obj
  WHILE @@FETCH_STATUS = 0
  BEGIN
    if @class=42 set @stack = @stack + ';' + @obj
	else set @stack=reverse(substring(reverse(@stack),charindex(';', reverse(@stack))+1,100000))
	if @prevt is not null print substring(@prevstack,2,100000)+' '+convert(varchar,datediff(ms,@prevt,@t))
	set @prevt=@t
	set @prevstack=@stack
    FETCH NEXT FROM my_cur INTO @r, @class, @t, @lev, @obj
  END
  CLOSE my_cur
  DEALLOCATE my_cur
GO