June 5, 2014 at 9:33 am
Hi
I wish to capture how long a certain stored proc is running for on our system. The sp is triggered by another another department within the businesss every morning.
Do I just create a server side trace using Profiler using the 'TSQL_Duration' template which captures the following events:
Stored Procedures - RPC completed and
TSQL - SQL BatchCompleted
and can I filter for a specific stored proc as I only want to capture the details of the offending item ?
June 5, 2014 at 9:57 am
if you are only concerned with the stored proc and its statements you should only need:
Stored Procedures
RPC:Completed
SP:completed
SP:StmtCompleted
include the object name column and then add a filter on the object name column for your proc
/****************************************************/
/* Created by: SQL Server 2014 Profiler */
/* Date: 06/05/2014 10:54:22 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- 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
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
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, 3, @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, 34, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 28, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 34, 0, 6, N'enter proc name here'
-- 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
June 5, 2014 at 11:43 am
While you can use trace, extended events might be a better way to go. The filtering on trace events occurs after the event is captures while the filtering on extended events occurs before capturing. It makes for a much lighter impact on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2014 at 2:44 pm
Thanks for the advice guys.
I'm not to familiar with extended events in sql2008r2 - so if you have any good pointers on how to use them then great. I have used them in sql2012 and they seem so much easier to setup compared to sql2008 (which is what i'll be running this test on).
So for now I might go with the Profiler trace (unless extended events is easier to set up ??)
Thanks again guys
June 5, 2014 at 2:50 pm
Extended events is not easier than trace, but it's pretty easy and certainly has less impact on the server. Books Online will get you going.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2014 at 3:26 pm
Jonathan Kehayias wrote an add-in for ssms 2008 that will allow you to create extended event session in the GUI.
for SSMS 2008/2008R2 - http://extendedeventmanager.codeplex.com/
for SSMS 2012 - http://www.sqlskills.com/free-tools/sql-server-2012-extended-events-add-in/
here a post on how to use the add-in: http://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-12-of-31-using-the-extended-events-ssms-addin/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply