SQL Profiler:-

• SQL Profiler is a tool which allows system administrator to monitor events in the SQL server.
• This is mainly used to capture and save data about each event of a file or a table for analysis.
• Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
• For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.

We can do the following using SQL Server Profiler

• 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 particularEventclass.

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.