Process Trace File

 --- I believe Raymond Peacock is the author



-- variables
declare @trace nvarchar(1000), @filter nvarchar(1000)
select @trace = $(traceFile)
--'E:\Traces\SERVERNAME__sp_trace.trc'
--select @filter = 'p_chat'


-- read raw data
select *
into #trace
from ::fn_trace_gettable(@trace, DEFAULT)
where TextData is not null
and CONVERT(varchar(100), textdata) <> 'exec sp_reset_connection'


-- extract according to filter
select CASE WHEN TextData LIKE '%exec %' THEN CONVERT(varchar(1000), TextData) ELSE NULL END AS ProcName, HostName, cpu, reads, duration, starttime, CONVERT(varchar(1000), TextData) AS TextData
into #procs
from #trace
where textdata like case when @filter is not null then '%' + @filter + '%' else textdata end


-- Deletes
DELETE #procs WHERE textData LIKE '%msdb.dbo.%'
DELETE #procs WHERE TextData = ' if @@trancount > 0 commit tran'
DELETE #procs WHERE TextData = 'begin tran'


-- reformat
update #procs
SET procname = SUBSTRING(procname, CHARINDEX('exec dbo.', ProcName), 1000)
WHERE CHARINDEX('exec dbo.', ProcName) > 0

update #procs
set procname = REPLACE(ProcName, 'exec dbo.', '')

update #procs
set procname = LEFT(procname, charindex('@', ProcName)-1)
where CHARINDEX('@', procname) > 0


-- summary results by proc
select top 10 procname, COUNT(*) as calls, AVG(cpu) AS AvgCPU, AVG(Reads) AS AvgReads, AVG(duration) as duration, MIN(starttime) AS [Start], MAX(starttime) AS [End]
from #procs
group by procname
order by (COUNT(*) * AVG(Reads)) DESC


-- results for top 5 sprocs
select *
from #procs
where procname in (
    select top 1 procname
    from #procs
    where ProcName is not null
    group by procname
    order by (COUNT(*) * AVG(Reads)) DESC
)
order by StartTime


-- cleanup
DROP TABLE #trace
DROP TABLE #procs