– SQL Profiler helps in troubleshooting SQL performance problems by collecting metrics about the statements run on SQL Server
– Since it is run on server, running from our local client machines will bring the SQL Server, so DONT EVER RUN SQL PROFILER FROM YOUR LOCAL CLIENT MACHINES but instead use server side trace as discussed below.
Server side tracing
— similar functionality as is done from client ssms
Get a list of all events you wish to track from http://msdn.microsoft.com/en-us/library/ms186265.aspx.
The events are like
SQL:StmtCompleted – TSQL completed
SQL:BatchCompleted – Occurs when a Transact-SQL batch has completed
Lock:Deadlock – in a deadlock scenario
Lock:Timeout. SP:Recompile – SP recompile
Scan:Started – table scan
Lock:Escalation – fine lock getting converted to coarse-grain lock; like page lock escalating to table level lock
Sort Warnings: sort operations not fitting in memory and probably spilled to tempDB
Deadlock Graph : when a deadlock victim is choosen and terminated
For all of these events, we need to then select the columns that can be added
TextData, BinaryData, SPID, Duration, StartTime, EndTime,
Reads, Writes, CPU
It is all done using the TSQL statement – sp_trace_setevent
But the user need to have ALTER TRACE permission.
sample TSQL trace file:
[cc lang=”SQL”]
/***********************************************/
/* Server Side Trace */
/***********************************************/
— Declare variables
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxFileSize bigint
DECLARE @fileName NVARCHAR(128)
DECLARE @on bit
— Set values
SET @maxFileSize = 5
SET @fileName = N’C:\TestTrace’
SET @on = 1
— Create trace
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL
— If error end process
IF (@rc != 0) GOTO error
— Set the events and data to collect
EXEC sp_trace_setevent @TraceID, 41, 1, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 17, @on
— Set Filters
— filter1 include databaseId = 6
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6
— filter2 exclude application SQL Profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Profiler’
— Start the trace
EXEC sp_trace_setstatus @TraceID, 1
— display trace id for future references
SELECT TraceID=@TraceID
GOTO finish
— error trap
error:
SELECT ErrorCode=@rc
— exit
finish:
GO[/cc]
To summarize these are 4 events which will happen for setting up a new trace on SQL server
1. sp_trace_create – create the trace – file name, size and time to stop the trace etc., are given
2. sp_trace_setevent – decide event to trace and column that will need be captured
3. sp_trace_setfilter – add filters to the trace i.e include or exclude certain DBs or tables etc.,
4. sp_trace_setstatus – start/stop/close the trace
How to check trace results
[cc lang=”SQL”]
SELECT * FROM ::fn_trace_gettable(‘traceFileName’, DEFAULT)
[/cc]
0 Comments.