One of the common questions that I see in the newsgroups is how to trace SQL statements executed by SQL Server. When we say tracing, what we mean is the set of operations that is executing on the server. Why is this useful?? Many applications will execute a varied amount of SQL statements against a SQL Server and it is often useful to know what is happening at the server. For example, we might want to watch the time taken by each SQL statement or procedure that is being executed at the server. This is useful if we want to tune some bottlenecks of our application. The tool that allows you to watch what is happening at the server is called the SQL Profiler and it has many features!! In this article, we will see a simple example that shows how to see the various SQL statements executing on the server.

 

SQL Profiler is installed when you install the complete SQL Server or the SQL client tools. It is always recommended to execute the profiler on a machine in which SQL Server is not running. This is because, the profiling tool by itself takes some resources to do its job and thus executing it on the same machine where SQL Server is installed will cause performance issues with SQL Server. SQL Profiler consists of events and data columns. Events are well, events that you want to watch for and is divided into various categories. Some example events are: Cursors, Database, TSQL. Each type of event has different data columns associated with it and you'll need to make sure that you select the right combination of columns so as to capture the information that you need. The creators of SQL Profiler have understood the various combinations of events and data columns that you will need and have thus provided templates. A template is a collection of events, data columns and other settings. You can either use the provided templates, extend them to suit your needs or create your own templates.

 

Once you have identified the events and data columns, you are ready to go! As the profiler is runnning, based on the events that you've configured, the profiler starts collecting information. You can decide to either save the output to a file or to a database table. Saving a trace to a database file has many advantages, one of which is you can query the table for various pieces of information. For example, you can list all the queries that took more than 2 seconds to execute and concentrate on tuning them. Let's now see an example of how to use the profiler.

 

To start the profiler, choose the SQL Profiler program from the SQL Server program menu or type profiler at the Run dialog box. You will now see the profiler application. Choose File > New > Trace to start a new trace against a particular SQL Server. You will now see the Connect to SQL Server dialog box where you can select a particular server against which you want to trace. After you have chosen the server, you will now see the Trace Properties dialog box as shown in the following figure.

 

 

Almost all of the options in this dialog box are self explanatory. Note that you can define a name for your trace and choose a template that you want to use and then decide to save the trace to a file or a database table. For our example, let us assume that we want to know the time of execution of each TSQL statement. For this, choose the SQLProfilerTSQL_Duration template from the template name selection combo box. Choosing a template uses a predefined set of events and data columns. Let us see what has been selected for our example. The following figure shows the events tab:

 

 

Notice that two events have been selected: RPC:Completed and SQL:BatchCompleted. These two events correspond to stored procedure execution and plain TSQL execution and will record the time of execution of both of these. Let us now see what columns will be shown in the output. The following figure shows the data columns tab:

 

 

The important piece of information for us is Duration, TextData (which indicates the statement) and SPID (which indicates the process ID executing the statement). The final tab called Filters can be used to set filters on the events so that you can restrict when the events will be captured. For example, if you want to capture traces only on the pubs database you can specify so. For our example, we will not use this tab. Having choosen the events and data columns, click on the Run button to start the trace. You will now see an empty trace window with the columns that we configured and it is ready to capture our statements. Let us know execute some statements using Query Analyzer and see what happens. Execute the following statements one by one:

SELECT * FROM authors

CREATE PROCEDURE ListAllTitlesAndAuthors AS
BEGIN
	SELECT * FROM authors
	SELECT * FROM titles
END

EXEC ListAllTitlesAndAuthors

 

After you have finished executing all the above 3 statements switch to the profiler to see what has been captured. The following figure shows the output (note that the duration and SPID might be different in your computer).

 

 

Note that output. Also, as you select each line in the grid, the lower portion shows the statement that has been executed. The output of this lower portion can vary for each event. Note that our SELECT statement took 340ms to execute. You can use the green arrow button to start tracing and the red stop button to stop the trace.

 

Well, we have seen a simple example of tracing of SQL statements in this article. The information contained in this article is enough to get you started on tracing SQL statements and you should play around with the settings to get a hang of the power of Profiler. Have fun!!