How to capture queries, tables and fields using the SQL Server Profiler

[Former Member]

SQL Server Profiler Step by Step

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:

Figure 1.0

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

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.

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:

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

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:

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.

References:

http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

http://msdn.microsoft.com/pt-br/library/cc580638.aspx

http://pedrogalvaojunior.wordpress.com/2009/09/18/profiler-para-sql-server-20052008-express/

http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-to-sql-server-2008-profiler-2/

Regards,

Marcelo Silva Santos