Click here to Skip to main content
Click here to Skip to main content

SQL Server Profiler Step by Step

, 22 Feb 2008
Rate this:
Please Sign up or sign in to vote.
SQL Server Profiler Step by Step: SQL Server 2005

Introduction

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring T-SQL Statements of Database Engine. We can save and reuse the state at a later point of time.

  • We can do the following using SQL Server Profiler
    • Create a trace
    • Watch the trace results as the trace runs
    • Store the trace results in a table
    • Start, stop, pause, and modify the trace results as necessary
    • Replay the trace results
  • Use SQL Server Profiler to monitor only the events in which you are interested.

Menu Path: Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.

The following screen will come:

Screenshot - pic1.jpg

Figure 1.0

Click on <Connect> Button. New Properties Screen will come:

Screenshot - properties.jpg

Figure 1.1

It has two selection tabs:

  • General: It is used for general setting for Trace Database Engine.
  • Event: It is used to add or remove some selected event for monitor.

In General Section (as given in Figure 1.1), it is divided into four sections.

Section 1: In this section, you have to just specify the name of your trace, Trace provider name and server name are predefined and based upon your SQL Server.

And it is not editable.

Section 2: It is the template section. You can choose different type of Templates based upon your requirements. It is the configuration for trace. By default, it is "Standard (Default)" templates. Others templates are T-SQL, T-SQL Duration, T-SQL Reply, T-SQL SPs, etc. You can create your own custom Templates by selecting different Events and Event Class. It is saved as ".tdf" Extension.

Section 3: This section is related to save your trace. Either as File (.trc) or in a database. as table. While clicking on Save to file check box, File save dialog box should open and you can save that file (with .trc extension).

If you check the "Save to Table", it will connect with your server and ask you to which database you want to save that trace table information.

Screenshot - savetotable.jpg

Figure 1.2

Section 4: You can stop your trace on a particular time. Check the "Enable trace stop time" checkbox and give the time at which you want to stop track, SQL Server will automatically stop trace on that time.

Now Move To "Event Section" Tab.

Now we need to know some definition with respect to SQL Server Profiler.

What is an Event?

An Event is an action or operation that is performed in your SQL Server 2005 Database Engine.

Some examples of Events are:

    • Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
    • User login and logout
    • Execution of Stored procedures
    • Operation with cursor

SQL Server profiler is able to trace all of these events and all these events are categories on a particular Event class.

What is an Event Class?

Event class is a type of event that can be traced.

Some examples are:

  • SQL: BatchCompleted
  • SQL: Batch Starting
  • Audit Login
  • Audit Logout
  • Lock: Acquired
  • Lock: Released

Now you can select events from this screen:

Screenshot - events.jpg

Figure 1.3

In section 1, we can select the proper check box based upon our requirement, section 2 will display the details of Selected events and Events class. If you check in the check box of section 3, you will get all the list of Events and Columns in Section 1.

Section 4 is something like customization. Just click on the "Column Filter Button". In this section, you can specify some condition (like or Not like).

Screenshot - Filter1.jpg

Figure 1.4

By clicking on "Organize Column" button, you can change the sequence of order of selected events.

Now Click on the "Run" Button, then Trace window will come:

Screenshot - trace1.jpg

Screenshot - trace2.jpg

Figure 1.5

Using these windows, you will get the detailed time duration of a query and all other events information that you have selected.

You can save this result and use it in future. Or you can extract a particular query from the trace, just right click and click on "Extract Event Data". And save this as a SQL Script.

Reply in SQL Server Profiler

SQL Server profiler has a Reply facility which has the ability to save a trace and replay it later.

Replay is useful to troubleshoot an application. Trace replay supports debugging by using Toggle Breakpoint and the Run to Cursor options on the SQL Server Profiler Replay menu.

Anything changed in SQL Server Management Studio will be traced by the SQL Profiler. So it can basically be used for database performance check. We also have "SQL Server Performance Monitor" to monitor the System and Server performance too.

History

  • Initial post: 20/11/2007

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abhijit Jana
Technical Lead
India India
.NET Consultant | Former Microsoft MVP - ASP.NET | CodeProject MVP, Mentor, Insiders| Technology Evangelist | Author | Speaker | Geek | Blogger | Husband
 
Blog : http://abhijitjana.net
Web Site : http://dailydotnettips.com
Twitter : @AbhijitJana
My Kinect Book : Kinect for Windows SDK Programming Guide
Follow on   Twitter

Comments and Discussions

 
GeneralThe images do not load for me Pinmemberbjdodo4-Dec-07 3:10 
GeneralRe: The images do not load for me PinmemberAbhijit Jana4-Dec-07 17:26 
i dont no what happend with CP. None of the image is coming. i will try to upload the image in a zip files .
 
Thanks !!!
 

Best Regards
-----------------
Abhijit Jana
View My CodeProject Articles

"Success is Journey it's not a destination"

GeneralRe: The images do not load for me PinmemberAbhijit Jana6-Dec-07 0:08 
GeneralRe: The images do not load for me Pinmemberbjdodo7-Dec-07 8:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 23 Feb 2008
Article Copyright 2007 by Abhijit Jana
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid