Tag Archives: Server Side Trace

Read SQL Server Profiler Trace files with SELECT statement

ReadRead SQL Server Profiler Trace files with SELECT statement

Earlier I have written article about how to automate process to start and stop SQL Server Profiler Trace. Server side tracing generates .TRC file which is hard to read if you do it manually.

I like set based data and it is great if we can execute SELECT statement on trace file so that either we can see properly managed result set or store the result in SQL Server table by INSERT….SELECT command or by SELECT….INTO command.

As I said trace files are very powerful weapon of DBA and they can’t afford to make distance of it. Trace file can keep close eyes on each batch / query / SP runs in your database for 24*7 and helps you to debug the issues. Trace file always helps me a lot in finding the performance related issues.

I will use the same trace file of AdventureWorks2012 database which I have generated from my earlier article. You can generate same kind of file from the article or you can have your own trace file.

Here is the SELECT statement I use:


SELECT

ServerName

,DatabaseName

,SPID AS SessionId

,ObjectName

,EventClass AS EventNumber

,RowCounts

,CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds

,CPU AS CPUInMiliseconds

,StartTime

,EndTime

,TextData

FROM fn_trace_gettable('D:\TraceCollection\AdventureWorks2012.trc', DEFAULT)

GO

This script is very easy yet powerful and part of my day-to-day DBA duty.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Start and stop server side profiler trace automatically in SQL Server 2012

StartStopStart and stop server side trace profiler automatically in SQL Server 2012

Profiler is a very powerful and old weapon for DBA and DBA can’t afford to make distance with this powerful weapon.  Profiler shows which TSQL statement/batch or SP is being processed by SQL Server, how it resolve internally and what efforts SQL Server has to make in order to execute that batch/SP. It shows duration of batch/TSQL/SP etc. along with CPU time, IO and so many other vital information which is mandatory to dig the issues which come across the way in DBA day-to-day life.

DBA can’t keep his eye personally on each database/instance 24*7 but server side trace (Profiler) can do it for you very easily. It adds little overhead on server but keep trust on my words it is really worth.

You can run trace by Profiler GUI as well as from server side trace. I least prefer GUI as it adds more overhead especially if you are not running it directly from the server where SQL Server installed. We won’t discuss pro/cons of profiler GUI and server side trace as it is beyond the scope of this article but we will discuss how we can automate server side trace process.

I will create two stored procedure out of which one SP will start trace and second SP will stop trace for my AdventureWorks2012 database.

I generally prefer to run trace during peak business hours. For me, it is 10AM to 7PM so I can set one SQL Server Agent job which calls one SP at 10AM so that my trace starts at 10AM sharp and second job which call second SP which will stop trace at 7PM sharp. It doesn’t need human interaction.

Let us see the script of both SPs.

1st SP should have following code:

Note: you can add/remove column as well as event as per your need. My SP is capturing some important column for SP:Complete event only.

 CREATE PROCEDURE [StartTrace]
 AS
 BEGIN
 DECLARE @trc INT
 DECLARE @TraceID INT
 DECLARE @maxfilesize BIGINT

SET @maxfilesize = 100

DECLARE @file NVARCHAR(500)
 SELECT @file = N'D:\TraceCollection\AdventureWorks2012'

EXEC @trc = SP_TRACE_CREATE
 @traceid = @TraceID output,
 @options = 2,  --TRACE_FILE_ROLLOVER
 @tracefile = @file,
 @maxfilesize = @maxfilesize,
 @stoptime  = NULL,
 @filecount = 10000

-- Set the events
 --Event number 43 is SP:Complete
 --right after 43, we have number like 1, 12, 14 etc. which are column numbers of event
 --you can get complete list of event and column number from here
 --http://msdn.microsoft.com/en-us/library/ms186265.aspx
 DECLARE @on BIT, @ret INT
 SET @on = 1
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 1, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 13, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 14, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 15, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 18, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 28, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 34, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 35, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 48, @on
 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 59, @on

EXEC SP_TRACE_SETFILTER
 @TraceID,
 28,  -- 28 is a column number for "Object Type"
 0,            -- and
 0,            -- ==
 8272 --we are filtering that only SPs should come and store in trace file

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

END
 GO

Now second SP this will be called by second job which will stop the trace:

 CREATE PROCEDURE [StopTrace]
 AS
 BEGIN
 DECLARE @trace_id INT;

SELECT @trace_id = id FROM sys.traces WHERE path LIKE  'D:\TraceCollection\AdventureWorks2012%';

IF @trace_id IS NOT NULL BEGIN
 --             Stops the specified trace.
 EXEC sp_trace_setstatus @trace_id, 0;
 -- Closes the specified trace and deletes its definition from the server.
 EXEC sp_trace_setstatus @trace_id, 2;
 END
 END
 GO

I will cover topic about how to read this .TRC file generated by startTrace SP in future article.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.