SQL Server – Find the expensive queries with Server Side Trace

With the help of this post sharing with you the procedure to find the expensive queries with Server Side trace.

First we see how to create a server side trace script.

• Run the Profiler Trace as mentioned here
• Go to the File >> Export >> Script Trace Definition >> For SQL Server 2000 or For SQL Server 2005- 2008R2

 

You will get a Script for Server Side Trace as

/****************************************************/
/* Created by: SQL Profiler                         */
/* Date: 01/11/2011  03:11:49 PM         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

DECLARE @EndTime DATETIME
DECLARE @OutputFileName NVARCHAR(256)

SET @OutputFileName = 'c:\MyFolder\MyTrace\SS_Trace' +

    CONVERT(VARCHAR(20), GETDATE(),112) +

    REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

SET @EndTime = DATEADD(mi,30,getdate())

exec @rc = sp_trace_create @TraceID output, 0,
@OutputFileName, @MaxFileSize, @EndTime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 15, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Run the Server Side Trace with the above script

Note:

• Set the Time in the Script as required
• Set the Maximum file Size
• Set the Output file location

Now we will export the Trace file data into table for analysis (to find the expensive queries)

CREATE   TABLE  TraceData (

 TextData Nvarchar(4000),

 ApplicationName varchar(200),

 Duration INT,

 Reads INT,

 Writes INT,

 CPU INT,

 StartTime DATETIME,

 EndTime DATETIME,

)

GO

INSERT INTO TraceData

(TextData,ApplicationName,Duration, Reads, Writes, CPU, StartTime,EndTime)

SELECT LEFT(CAST(TextData as varchar(2000)),2000), ApplicationName,
Duration/1000 as Duration,Reads, Writes, CPU, StartTime,EndTime
FROM :: fn_trace_gettable('c:\MyFolder\MyTrace\SS_Trace20111031104212.trc',1)

Note:

• Dividing the duration by 1000 to get the value in sec
• Using the LEFT function to truncate the very long script for insertion in TraceData

Now we can find the expensive queries as

SELECT * FROM TraceData ORDER BY DURATION DESC

Further, you can fine the specific query or SP as

SELECT * FROM TraceData WHERE TEXTDATA LIKE '%SomeString%'
ORDER BY DURATION DESC

I hope this will help you to find the expensive queries.