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.

[sourcecode language=”sql”]
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[/sourcecode]

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

[sourcecode language=”sql”]
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[/sourcecode]

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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Start and stop server side profiler trace automatically in SQL Server 2012”

Comments are closed.