Pages

Tuesday, 2 October 2012

SQL Server : Setting up Server Side Trace

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. 
  1. Defining a trace.
  2. Defining the events and columns to captured.
  3. Defining the filter condition.
  4. 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 @TraceID1014@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

   --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 

17 comments:

  1. Great post. This would reaaly help lot of Eva's.

    ReplyDelete
  2. Quick 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?

    ReplyDelete



  3. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

    ReplyDelete
  4. ERPTREE handles oracle fusion HCM training and its modules maintaining classroom based training with
    the 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

    ReplyDelete
  5. Nice Blog.Thank you for Sharing. We are leading erp software software solution providers in chennai. For more details call +91 9677025199.
    erp software in chennai | erp providers in chennai | online events registration

    ReplyDelete
  6. https://www.behance.net/gallery/46472895/_
    https://www.behance.net/gallery/46463613/_
    https://www.behance.net/gallery/46463247/_
    https://www.behance.net/gallery/46451097/_
    https://www.behance.net/gallery/46460639/_
    https://www.behance.net/gallery/46462575/_

    ReplyDelete
  7. https://www.behance.net/gallery/46450923/_
    https://www.behance.net/gallery/46450419/_
    https://www.behance.net/gallery/46430977/-jumperadscom
    https://www.behance.net/gallery/42972037/_
    https://www.behance.net/gallery/40396873/Transfer-and-relocation-and-Furniture-in-Dammam
    https://www.behance.net/gallery/51576047/_

    ReplyDelete
  8. Interesting post! This is really helpful for me. I like it! Thanks for sharing!

    Webseiten Gestaltung Lüdenscheid

    ReplyDelete
  9. Interesting post! This is really helpful for me. I like it! Thanks for sharing!
    seo lüdenscheid

    ReplyDelete
  10. Your website content nice nice and interesting to observe.
    Internet Marketing Dienstleistungen

    ReplyDelete

  11. ارخص نقل اثاث بالدمام ارخص نقل اثاث بالدمام
    ارخص نقل عفش بالرياض ارخص نقل عفش بالرياض
    دينا نقل عفش بالرياض دينا نقل عفش بالرياض
    نقل عفش من المدينة المنورة الى مكة نقل عفش من المدينة المنورة الى مكة
    نقل عفش من الدمام الى جدة نقل عفش من الدمام الى جدة
    شحن عفش من جدة الى الاردن شحن عفش من جدة الى الاردن

    ReplyDelete

  12. ان الرائد تقدم افضل الخدمات النزلية في المدينة المنورة بارخص الاسعار يمكنك زيارة التالي للمزيد من المعلومات :
    شركة تنظيف بمكة
    شركة نقل عفش من جدة الى الاردن شركة شحن عفش من جدة الى الاردن
    شركة تنظيف منازل بالخبر

    افضل شركة تنظيف منازل بالمدينة المنورة شركة تنظيف منازل بالمدينة المنورة
    شركة مكافحة حشرات بالمدينة المنورة شركة رش حشرات بالمدينة المنورة

    ReplyDelete
  13. imo
    imo downloading
    imo download
    imo app
    imo apk
    We recently launched our institute in the USA and getting the best reputation over there.

    ReplyDelete
  14. عندما يكون هناك ارتباط بين عملية التنظيف في مكة شركة تنظيف منازل بمكة وتنظيف جدة في شركة تنظيف منازل بجدة وبين عملية انتشار الأمراض والجراثيم تتخلص منها شركة مكافحة حشرات بمكة المكرمة في مكة وفي جدة شركة مكافحة حشرات بجدة فيلزم ان تتخلص من الحشرات والأوساخ في الطائف من شركة مكافحة حشرات بالطائف كما يجب ان يتم التخلص من جميع الأتربة والأوساخ في خزانات المياه في جدة من قبل شركة تنظيف خزانات بجدة وخزانات المدينة المنورة من جهة شركة تنظيف خزانات بالمدينة المنورة وكذلك يجب ان المكان مثل الشقة او الفيلا او المنزل الذي نعيش بداخله شركة مكافحة حشرات بالطائف حتى يتم حماية أطفالنا من خطر الإصابة بالأمراض وتوفر جميع خدمات شركة تنظيف منازل بجدة وخدمات التنظيف سواء التنظيف العادي او خدمات غسيل خزانات المياه كما في شركة صيانة خزانات بجدة و شركة عزل خزانات بجدة من أهم شركات مكافحة الحشرات المتخصصة وما يتم القيام به لخروج الخدمة بصورة مشرفة أمام العملاء

    ReplyDelete
  15. gamekiller
    gamekiller app
    game killer
    gamekiller apk
    gamekiller download
    We are leading erp software software solution providers in chennai. For more details.

    ReplyDelete