Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server

SQL Server Profiler Step by Step

Rate me:
Please Sign up or sign in to vote.
3.96/5 (54 votes)
22 Feb 2008CPOL4 min read 429.8K   65   14
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)


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

Comments and Discussions

 
QuestionHow to identify events based on requirements Pin
Muhammad Ali Khamis8-Jun-16 0:06
Muhammad Ali Khamis8-Jun-16 0:06 
Questionsql profiler in sql server 2008 R2 Pin
J{0}Y23-Jan-15 20:23
J{0}Y23-Jan-15 20:23 
QuestionSQL Server Profiler Step by Step - Abhijit Jana Pin
Member 972113316-Jun-14 0:47
Member 972113316-Jun-14 0:47 
Questionhi abhijit jana Pin
gudapati327-May-14 23:59
gudapati327-May-14 23:59 
GeneralMy vote of 2 Pin
cboozb9-May-14 12:47
cboozb9-May-14 12:47 
GeneralGood Pin
Praveen P N19-Oct-13 16:20
Praveen P N19-Oct-13 16:20 
SuggestionNot enough for an article Pin
Joezer BH18-Jun-13 3:44
professionalJoezer BH18-Jun-13 3:44 
GeneralMy vote of 5 Pin
Amit Gill28-May-12 22:54
Amit Gill28-May-12 22:54 
GeneralThe images do not load for me Pin
bjdodo4-Dec-07 3:10
bjdodo4-Dec-07 3:10 
GeneralRe: The images do not load for me Pin
Abhijit Jana4-Dec-07 17:26
professionalAbhijit Jana4-Dec-07 17:26 
GeneralRe: The images do not load for me Pin
Abhijit Jana6-Dec-07 0:08
professionalAbhijit Jana6-Dec-07 0:08 
GeneralRe: The images do not load for me Pin
bjdodo7-Dec-07 8:40
bjdodo7-Dec-07 8:40 
GeneralTrace stored procedures Pin
canozurdo20-Nov-07 3:32
canozurdo20-Nov-07 3:32 
GeneralRe: Trace stored procedures Pin
Abhijit Jana20-Nov-07 17:40
professionalAbhijit Jana20-Nov-07 17:40 

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

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