For a DBA, SQL profiler is a great tool to trouble shoot performance issues. It is a pain to configure a trace and analyse it using the profiler GUI . I usually use server side trace which can be configured by running set of scripts. Server side traces are much lighter as it does not need to transfer data over the network.In this post, let us discuss about configuring server side trace.
There are three steps in creating server side trace.
- Defining a trace.
- Defining the events and columns to captured.
- Defining the filter condition.
- Start the trace.
Please find below the script used to configure a server side trace.Refer BOL for more option about adding events,columns and filter condition.
/****************************************************************************************
STEP 1 : DEFINING THE TRACE
***************************************************************************************/
SET NOCOUNT ON;
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @MaxFileSize BIGINT
DECLARE @OutputFileName NVARCHAR(256)
SET @MaxFileSize = 1024
--Replace The H:\MyTraces with a valid folder in your environment
SET @OutputFileName = 'D:\MyTraces\FileTrace' + CONVERT(VARCHAR(20), GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
--sp_trace_create @traceid,@options,@tracefile,@maxfilesize,@stoptime ,@filecount
EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @OutputFileName, @MaxFileSize, NULL,5
/****************************************************************************************
STEP 2 : DEFINING THE EVENT AND COLUMNS
*****************************************************************************************/
DECLARE @Status bit
SET @Status = 1
--sp_trace_setevent @traceid ,@eventid ,@columnid,@on
--RPC:Completed event
EXEC sp_trace_setevent @TraceID, 10, 16, @Status
EXEC sp_trace_setevent @TraceID, 10, 1, @Status
EXEC sp_trace_setevent @TraceID, 10, 17, @Status
EXEC sp_trace_setevent @TraceID, 10, 14, @Status
EXEC sp_trace_setevent @TraceID, 10, 18, @Status
EXEC sp_trace_setevent @TraceID, 10, 12, @Status
EXEC sp_trace_setevent @TraceID, 10, 13, @Status
EXEC sp_trace_setevent @TraceID, 10, 8, @Status
EXEC sp_trace_setevent @TraceID, 10, 10, @Status
EXEC sp_trace_setevent @TraceID, 10, 11, @Status
EXEC sp_trace_setevent @TraceID, 10, 35, @Status
--SQL:BatchCompleted event
EXEC sp_trace_setevent @TraceID, 12, 16, @Status
EXEC sp_trace_setevent @TraceID, 12, 1, @Status
EXEC sp_trace_setevent @TraceID, 12, 17, @Status
EXEC sp_trace_setevent @TraceID, 12, 14, @Status
EXEC sp_trace_setevent @TraceID, 12, 18, @Status
EXEC sp_trace_setevent @TraceID, 12, 12, @Status
EXEC sp_trace_setevent @TraceID, 12, 13, @Status
EXEC sp_trace_setevent @TraceID, 12, 8, @Status
EXEC sp_trace_setevent @TraceID, 12, 10, @Status
EXEC sp_trace_setevent @TraceID, 12, 11, @Status
EXEC sp_trace_setevent @TraceID, 12, 35, @Status
/****************************************************************************************
STEP 3 : DEFINING THE Filter condition
*****************************************************************************************/
--sp_trace_setfilter @traceid ,@columnid,@logical_operator,@comparison_operator,@value
EXEC sp_trace_setfilter @TraceID,8,0,0,N'MyAppServer' --Hostname
EXEC sp_trace_setfilter @TraceID,35,0,0,N'MyDB' --Database name
EXEC sp_trace_setfilter @TraceID,11,0,0,N'MyAppUser' --SQL login
/****************************************************************************************
STEP 4 : Start the trace
*****************************************************************************************/
EXEC sp_trace_setstatus @TraceID, 1
/****************************************************************************************
Display the trace Id and traceFilename
*****************************************************************************************/
SELECT @TraceID,@OutputFileName
Now the trace is running and you can verify the currently running trace using the below query
SELECT * FROM ::fn_trace_getinfo(NULL)
Once it ran for the desired time , you can stop the trace using the below script
SELECT * FROM ::fn_trace_getinfo(NULL)
Once it ran for the desired time , you can stop the trace using the below script
--sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
DECLARE @traceid INT
DECLARE @status INT
SET @traceid =2
SET @status =0
EXEC sp_trace_setstatus @traceid,@status
SET @status =2
EXEC sp_trace_setstatus @traceid,@status
To view the content of the trace file. If you have added more column in the trace , add that column in the below select statement also.You can insert the output into a table for further analysis of the trace.SELECT
TextData, Duration/1000, Reads, Writes, CPU, StartTime,HostName,ApplicationName,LoginName,DatabaseName
FROM fn_trace_gettable('D:\MyTraces\FileTrace20120929023408.trc',1)
Below query will help us to list the events and columns capturing as part of a trace.
SELECT
t.EventID,
t.ColumnID,
e.name AS Event_Description,
c.name AS Column_DescriptionFROM ::fn_trace_geteventinfo(2) t
--Change the trace id to appropriate one JOIN sys.trace_events e ON t.eventID = e.trace_event_id
JOIN sys.trace_columns c ON t.columnid = c.trace_column_id
If you liked this post, do like my page on FaceBook
Great post. This would reaaly help lot of Eva's.
ReplyDeleteQuick question -- if the date is set dynamically with GETDATE, and ROLLOVER files are enabled, will each rollover file have a different date timestamp in the file name or is that value only generated once?
ReplyDeleteERPTREE handles oracle fusion HCM training and its modules maintaining classroom based training with
ReplyDeletethe self-paced videos. An expert having ten plus years of self-experience handles the training period through online and explains each and every point perfectly. We recently launched our institute in the USA and getting the best reputation over there.
Oracle fusion HCM Online Training
Nice Blog.Thank you for Sharing. We are leading erp software software solution providers in chennai. For more details call +91 9677025199.
ReplyDeleteerp software in chennai | erp providers in chennai | online events registration
Interesting post! This is really helpful for me. I like it! Thanks for sharing!
ReplyDeleteseo lüdenscheid
ReplyDeleteارخص نقل اثاث بالدمام ارخص نقل اثاث بالدمام
ارخص نقل عفش بالرياض ارخص نقل عفش بالرياض
دينا نقل عفش بالرياض دينا نقل عفش بالرياض
نقل عفش من المدينة المنورة الى مكة نقل عفش من المدينة المنورة الى مكة
نقل عفش من الدمام الى جدة نقل عفش من الدمام الى جدة
شحن عفش من جدة الى الاردن شحن عفش من جدة الى الاردن
imo
ReplyDeleteimo downloading
imo download
imo app
imo apk
We recently launched our institute in the USA and getting the best reputation over there.
gamekiller
ReplyDeletegamekiller app
game killer
gamekiller apk
gamekiller download
We are leading erp software software solution providers in chennai. For more details.
لو تريد شركة تنظيف خزانات متخصصة فما عليك الا التعامل مع
ReplyDeleteشركة تنظيف خزانات بجدة
شركة تنظيف خزانات بالمدينة المنورة
شركة تنظيف خزانات بمكة المكرمة
شركة تنظيف خزانات
شركة تنظيف خزانات بالطائف
تقوم بجميع خدمات التنظيف ومنها تنظيف الخزان سواء كانت العلوية أو السفلية وتعقيمها تعقيم تام بأفضل أنواع المطهرات
ReplyDeleteشركة تنظيف منازل بجدة
شركة تنظيف منازل بمكة
افضل شركة تنظيف بجدة
شركة تنظيف منازل
Perusing this article gave me numerous things to consider. You have some quality data here that any peruser would appreciate. I share huge numbers of your perspectives in this article.
ReplyDeleteSEO services in kolkata
Best SEO services in kolkata
SEO company in kolkata
Best SEO company in kolkata
Top SEO company in kolkata
Top SEO services in kolkata
SEO services in India
SEO copmany in India
I for one think your article is intriguing, fascinating, and astonishing. I share a portion of your equivalent convictions on this subject. I like your composing style and will return to your site.
ReplyDeleteOnline Teaching Platforms
Online Live Class Platform
Online Classroom Platforms
Online Training Platforms
Online Class Software
Virtual Classroom Software
Online Classroom Software
Learning Management System
Learning Management System for Schools
Learning Management System for Colleges
Learning Management System for Universities
joomla development company delhi
ReplyDelete• ppt sharing sites list
ReplyDeleteHi Nice Blog post thanks for sharing
ReplyDeleteEar Wax Removal
Thanks for sharing your expertise! Your writing is always so engaging and informative. best seo services in gwalior
ReplyDeleteFantastic article! Your straightforward approach and insightful content made it highly readable. I’m excited to follow your upcoming posts! vehicle check
ReplyDelete