if objectproperty( object_id( 'sp_ParseTrcShowStats' ), 'IsProcedure' ) is not null
drop procedure sp_ParseTrcShowStats
go
create procedure sp_ParseTrcShowStats
( @TrcFile nvarchar(1000),
@SPID int = null, @StartTime datetime = null, @EndTime datetime = null,
@Debug bit = 0 )
as
begin
/********************************************************************************/
/* Created By : Umachandar Jayachandran (UC) */
/* Created On : 27 July 2002 */
/* Description : This SP can be used to parse the "Show Plan Statistics" event */
/* from a trace file. The show plan output in text format is */
/* stored as a BLOB in the "BinaryData" column. The code to parse*/
/* the column value and obtain the show plan output uses several */
/* of the utility functions found under the SQL2000 page in my */
/* web site. */
/* This demonstrates also demonstrates techniques to parse image */
/* data in T-SQL. To convert binary values of floating point */
/* numbers, two UDFs are used: "IEEE32ToReal_" & "IEEE64ToFloat".*/
/********************************************************************************/
/* Resources : https://umachandar.com/resources.htm */
/********************************************************************************/
declare @TrcKey int, @DataRowStart int, @ShowAllStatsCur cursor,
@NumCols int, @NumRows int, @RowCnt int,
@ColPos int, @ColLen int, @ColPad int, @ColCnt int, @Offset int,
@ColValue nvarchar(4000), @ColList nvarchar(4000),
@LenPos int, @LenPad int, @MaxRowLength int, @DataType tinyint, @DataLen int
declare @Offsets table
( "TrcKey" int not null, "RowNum" int not null, "ColNum" int not null, "Offset" int not null, "Length" int not null,
primary key( "TrcKey", "RowNum", "ColNum" ) )
select identity(int) as TrcKey, BinaryData, NTUserName, NTDomainName, HostName, ClientProcessID, ApplicationName, LoginName, SPID, StartTime
into #TrcData
from ::fn_trace_gettable( @TrcFile, default)
where EventClass = 98 -- Process only "Show Plan Statistics" event.
and SPID between coalesce( @SPID, 1 ) and coalesce( @SPID, 2147483647 )
and StartTime between coalesce( @StartTime, '1753-1-1' ) and coalesce( @EndTime, '9999-12-31' )
alter table #TrcData add primary key ( Trckey )
set @ShowAllStatsCur = cursor fast_forward for select TrcKey from #TrcData
open @ShowAllStatsCur
while( 'fetch is ok' = 'fetch is ok' )
begin
fetch @ShowAllStatsCur into @TrcKey
if @@fetch_status < 0 break
set @RowCnt = 1
set @ColCnt = 1
set @MaxRowLength = 0
set @ColList = ''
set @ColPad = 12
set @ColPos = 21
set @LenPad = 12
set @LenPos = 17
set @ColLen = ( select substring( BinaryData, @LenPos, 1 ) from #TrcData where TrcKey = @TrcKey )
set @NumCols = ( select substring( BinaryData, 1, 1 ) from #TrcData where TrcKey = @TrcKey )
set @NumRows = ( select substring( BinaryData, 2, 4 ) from #TrcData where TrcKey = @TrcKey )
if @Debug = 1
raiserror( 'TrcKey: [%d], Number of columns: [%d], Number of rows: [%d].', 0, 1,
@TrcKey, @NumCols, @NumRows )
-- We can ignore the actual header row since we know the format of the result set.
-- To make it more dynamic, we could parse the header row & create the table itself.
-- Code shows how to get the header row information and find the maximum length of the data row, column names, data types etc.
set @DataRowStart = 620
while @ColCnt <= @NumCols
begin
select @ColLen = substring( BinaryData, @LenPos, 1 ),
@ColValue = substring( BinaryData, @ColPos, @ColLen ),
@DataType = substring( BinaryData, @LenPos - 8, 1 ),
@DataLen = substring( BinaryData, @LenPos - 3, 1 ) +
substring( BinaryData, @LenPos - 4, 1 )
from #TrcData
where TrcKey = @TrcKey
select @ColCnt = @ColCnt + 1,
@LenPos = @LenPos + @LenPad + @ColLen,
@ColPos = @ColPos + @ColLen + @ColPad,
@ColList = @ColList + case @ColList when '' then '' else ', ' end + @ColValue,
@MaxRowLength = @MaxRowLength + sign( @MaxRowLength ) +
case
when @DataLen/2 = 0 then 10
when ( case
when @DataType in ( 0x26, 0x38, 0x32, 0x6D ) -- Fixed length integer data types
or @DataLen/2 < 10 then 10 -- This is the minimum length
else @DataLen/2
end ) > @ColLen/2 then @DataLen/2
else @ColLen/2
end
end
if @Debug = 1
raiserror( 'Column list: [%s].', 0, 1, @ColList )
if @Debug = 1
raiserror( 'Maximum row length for header: [%d].', 0, 1, @MaxRowLength )
-- Parse offsets for each data row.
while @RowCnt <= @NumRows
begin
set @ColCnt = 1
while @ColCnt <= @NumCols
begin
set @DataLen = ( select substring( BinaryData, @DataRowStart + 3, 1 ) +
substring( BinaryData, @DataRowStart + 2, 1 ) +
substring( BinaryData, @DataRowStart + 1, 1 ) +
substring( BinaryData, @DataRowStart + 0, 1 )
from #TrcData
where TrcKey = @TrcKey )
set @Offset = @DataRowStart + 4
if @Debug = 1
raiserror( 'Column: [%d], DataRowStart = [%d], Offset = [%d], Length = [%d].', 0, 2,
@ColCnt, @DataRowStart, @Offset, @DataLen )
insert into @OffSets
( "TrcKey", "RowNum", "ColNum", "Offset", "Length" )
values( @TrcKey, @RowCnt, @ColCnt, @Offset, case when @DataLen > 8000 then 8000 else @DataLen end )
set @DataRowStart = @DataRowStart + @DataLen + 4
set @ColCnt = @ColCnt + 1
end
set @DataRowStart = @DataRowStart + 1
set @RowCnt = @RowCnt + 1
end
end
select s."SPID", s."StartTime",
s."Rows", s."Executes", s."StmtText", s."StmtId", s."NodeId", s."Parent",
s."PhysicalOp", s."LogicalOp", s."Argument", s."DefinedValues",
s."EstimateRows", s."EstimateIO", s."EstimateCPU", s."AvgRowSize", s."TotalSubtreeCost",
s."OutputList", s."Warnings", s."Type", s."Parallel", s."EstimateExecutions",
s."NTUserName", s."NTDomainName", s."HostName", s."ClientProcessID", s."ApplicationName", s."LoginName"
from (
select o."RowNum", t."SPID", t."StartTime",
cast( substring( BinaryData, o.O01 + 7, 1 ) + substring( BinaryData, o.O01 + 6, 1 ) +
substring( BinaryData, o.O01 + 5, 1 ) + substring( BinaryData, o.O01 + 4, 1 ) +
substring( BinaryData, o.O01 + 3, 1 ) + substring( BinaryData, o.O01 + 2, 1 ) +
substring( BinaryData, o.O01 + 1, 1 ) + substring( BinaryData, o.O01 + 0, 1 ) as bigint ),
cast( substring( BinaryData, o.O02 + 7, 1 ) + substring( BinaryData, o.O02 + 6, 1 ) +
substring( BinaryData, o.O02 + 5, 1 ) + substring( BinaryData, o.O02 + 4, 1 ) +
substring( BinaryData, o.O02 + 3, 1 ) + substring( BinaryData, o.O02 + 2, 1 ) +
substring( BinaryData, o.O02 + 1, 1 ) + substring( BinaryData, o.O02 + 0, 1 ) as bigint ),
cast( substring( BinaryData, o.O03, o.L03 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O04 + 3, o.L04 ) + substring( BinaryData, o.O04 + 2, o.L04 ) +
substring( BinaryData, o.O04 + 1, o.L04 ) + substring( BinaryData, o.O04 + 0, o.L04 ) as int ),
cast( substring( BinaryData, o.O05 + 3, o.L05 ) + substring( BinaryData, o.O05 + 2, o.L05 ) +
substring( BinaryData, o.O05 + 1, o.L05 ) + substring( BinaryData, o.O05 + 0, o.L05 ) as int ),
cast( substring( BinaryData, o.O06 + 3, o.L06 ) + substring( BinaryData, o.O06 + 2, o.L06 ) +
substring( BinaryData, o.O06 + 1, o.L06 ) + substring( BinaryData, o.O06 + 0, o.L06 ) as int ),
cast( substring( BinaryData, o.O07, o.L07 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O08, o.L08 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O09, o.L09 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O10, o.L10 ) as nvarchar(4000) ),
dbo.IEEE32ToReal_( substring( BinaryData, o.O11 + 3, 1 ) + substring( BinaryData, o.O11 + 2, 1 ) +
substring( BinaryData, o.O11 + 1, 1 ) + substring( BinaryData, o.O11 + 0, 1 ) ),
dbo.IEEE64ToFloat_( substring( BinaryData, o.O12 + 7, 1 ) + substring( BinaryData, o.O12 + 6, 1 ) +
substring( BinaryData, o.O12 + 5, 1 ) + substring( BinaryData, o.O12 + 4, 1 ) +
substring( BinaryData, o.O12 + 3, 1 ) + substring( BinaryData, o.O12 + 2, 1 ) +
substring( BinaryData, o.O12 + 1, 1 ) + substring( BinaryData, o.O12 + 0, 1 ) ),
dbo.IEEE64ToFloat_( substring( BinaryData, o.O13 + 7, 1 ) + substring( BinaryData, o.O13 + 6, 1 ) +
substring( BinaryData, o.O13 + 5, 1 ) + substring( BinaryData, o.O13 + 4, 1 ) +
substring( BinaryData, o.O13 + 3, 1 ) + substring( BinaryData, o.O13 + 2, 1 ) +
substring( BinaryData, o.O13 + 1, 1 ) + substring( BinaryData, o.O13 + 0, 1 ) ),
cast( substring( BinaryData, o.O14 + 3, o.L14 ) + substring( BinaryData, o.O14 + 2, o.L14 ) +
substring( BinaryData, o.O14 + 1, o.L14 ) + substring( BinaryData, o.O14 + 0, o.L14 ) as int ),
dbo.IEEE64ToFloat_( substring( BinaryData, o.O15 + 7, 1 ) + substring( BinaryData, o.O15 + 6, 1 ) +
substring( BinaryData, o.O15 + 5, 1 ) + substring( BinaryData, o.O15 + 4, 1 ) +
substring( BinaryData, o.O15 + 3, 1 ) + substring( BinaryData, o.O15 + 2, 1 ) +
substring( BinaryData, o.O15 + 1, 1 ) + substring( BinaryData, o.O15 + 0, 1 ) ),
cast( substring( BinaryData, o.O16, o.L16 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O17, o.L17 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O18, o.L18 ) as nvarchar(4000) ),
cast( substring( BinaryData, o.O19 + 3, o.L19 ) + substring( BinaryData, o.O19 + 2, o.L19 ) +
substring( BinaryData, o.O19 + 1, o.L19 ) + substring( BinaryData, o.O19 + 0, o.L19 ) as int ),
dbo.IEEE32ToReal_( substring( BinaryData, o.O20 + 3, 1 ) + substring( BinaryData, o.O20 + 2, 1 ) +
substring( BinaryData, o.O20 + 1, 1 ) + substring( BinaryData, o.O20 + 0, 1 ) ),
NTUserName, NTDomainName, HostName, ClientProcessID, ApplicationName, LoginName
from #TrcData as t
join (
select o1.TrcKey, o1.RowNum,
min( case o1.ColNum when 01 then o1.Offset end ), min( case o1.ColNum when 01 then o1.Length end ),
min( case o1.ColNum when 02 then o1.Offset end ), min( case o1.ColNum when 02 then o1.Length end ),
min( case o1.ColNum when 03 then o1.Offset end ), min( case o1.ColNum when 03 then o1.Length end ),
min( case o1.ColNum when 04 then o1.Offset end ), min( case o1.ColNum when 04 then sign(o1.Length) end ),
min( case o1.ColNum when 05 then o1.Offset end ), min( case o1.ColNum when 05 then sign(o1.Length) end ),
min( case o1.ColNum when 06 then o1.Offset end ), min( case o1.ColNum when 06 then sign(o1.Length) end ),
min( case o1.ColNum when 07 then o1.Offset end ), min( case o1.ColNum when 07 then o1.Length end ),
min( case o1.ColNum when 08 then o1.Offset end ), min( case o1.ColNum when 08 then o1.Length end ),
min( case o1.ColNum when 09 then o1.Offset end ), min( case o1.ColNum when 09 then o1.Length end ),
min( case o1.ColNum when 10 then o1.Offset end ), min( case o1.ColNum when 10 then o1.Length end ),
min( case o1.ColNum when 11 then o1.Offset end ), min( case o1.ColNum when 11 then o1.Length end ),
min( case o1.ColNum when 12 then o1.Offset end ), min( case o1.ColNum when 12 then o1.Length end ),
min( case o1.ColNum when 13 then o1.Offset end ), min( case o1.ColNum when 13 then o1.Length end ),
min( case o1.ColNum when 14 then o1.Offset end ), min( case o1.ColNum when 14 then sign(o1.Length) end ),
min( case o1.ColNum when 15 then o1.Offset end ), min( case o1.ColNum when 15 then o1.Length end ),
min( case o1.ColNum when 16 then o1.Offset end ), min( case o1.ColNum when 16 then o1.Length end ),
min( case o1.ColNum when 17 then o1.Offset end ), min( case o1.ColNum when 17 then o1.Length end ),
min( case o1.ColNum when 18 then o1.Offset end ), min( case o1.ColNum when 18 then o1.Length end ),
min( case o1.ColNum when 19 then o1.Offset end ), min( case o1.ColNum when 19 then sign(o1.Length) end ),
min( case o1.ColNum when 20 then o1.Offset end ), min( case o1.ColNum when 20 then o1.Length end )
from @Offsets as o1
group by o1.TrcKey, o1.RowNum
) as o( TrcKey, RowNum,
O01, L01, O02, L02, O03, L03, O04, L04, O05, L05, O06, L06, O07, L07, O08, L08, O09, L09, O10, L10,
O11, L11, O12, L12, O13, L13, O14, L14, O15, L15, O16, L16, O17, L17, O18, L18, O19, L19, O20, L20 )
on o.TrcKey = t.TrcKey
) as s( "RowNum", "SPID", "StartTime",
"Rows", "Executes", "StmtText", "StmtId", "NodeId", "Parent",
"PhysicalOp", "LogicalOp", "Argument", "DefinedValues",
"EstimateRows", "EstimateIO", "EstimateCPU", "AvgRowSize", "TotalSubtreeCost",
"OutputList", "Warnings", "Type", "Parallel", "EstimateExecutions",
"NTUserName", "NTDomainName", "HostName", "ClientProcessID", "ApplicationName", "LoginName" )
order by s."StartTime", s."RowNum"
end
go
declare @TrcFile nvarchar(1000)
set @TrcFile = N'c:\Documents and Settings\Umachandar Jayachand\My Documents\SQL Server Workspace\TraceFile BinaryData Decode\ShowPlan Complex.trc'
-- set @TrcFile = N'c:\Documents and Settings\Umachandar Jayachand\My Documents\SQL Server Workspace\TraceFile BinaryData Decode\ShowPlan Simple.trc'
-- set @TrcFile = N'c:\Documents and Settings\Umachandar Jayachand\My Documents\SQL Server Workspace\TraceFile BinaryData Decode\Test1.trc'
exec sp_ParseTrcShowStats @TrcFile, default, default, default, default
/*
SPID StartTime Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
---- ----------------------- ----- -------- --------------------------------- ------ ------ ------ -------------- -------------- ---------------------- ------------- ------------ ----------- --------------------- ----------- ------------------ ------------------- -------- -------- -------- ------------------
54 2002-08-17 18:59:49.240 89600 1 |--Top(ROWCOUNT est 0) 0 1 0 Top Top 66117.414 0.0 6.6117415616430133E-3 5639 747.35920941497352 [sysobjects].[name] PLAN_ROW 0 1.0
54 2002-08-17 18:59:49.240 89600 1 |--Compute Scalar(DEFINE:... 0 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1044]...) [Expr1044]=.. 66117.414 0.0 6.6117415616430133E-3 5639 747.35259767341188 [sysobjects].[name] PLAN_ROW 0 1.0
*/
set @TrcFile = N'c:\Documents and Settings\Umachandar Jayachand\My Documents\SQL Server Workspace\TraceFile BinaryData Decode\Test2.trc'
-- Non-default parameters:
exec sp_ParseTrcShowStats @TrcFile, 51, '2002-8-17 20:58', '2002-8-18 21:00', default
This page was last updated on May 01, 2006 04:28 PM.