SQL Profiler

SQL Profiler allows you to monitor and capture events on an instance of SQL Server. You can configure it to capture all events or just a subset that you need to monitor. It lets you do the following:

• Capture Transact-SQL statements that are causing errors.

• Debug individual stored procedures or Transact-SQL statements.

• Monitor system performance.

• Collect the complete Transact-SQL load of a production system and replay it in your test environment.

SQL Profiler can collect external events initiated by end users (such as batch starts or login attempts), as well as internal events initiated by the system (such as individual Transact-SQL statements from within a stored procedure, table or index scans, objects locks, and transactions).

Using SQL Profiler

SQL Profiler is an MDI application that contains one or more trace windows. A trace window allows you to first configure events, filters, and data columns, and then to collect data from the server that is being audited.

After you start SQL Profiler, the first thing you should do is open a new trace window (File | New Trace) and select the server instance to be audited and the connection parameters. You will be prompted to specify the name of the trace, configure trace properties by choosing a predefined template (in the Use the Template list box), decide on a location of collected data, and predefine when tracing should be stopped:

There are numerous templates available, some of the most useful of which are the following:

• Standard (default) Collects information about connections, stored procedures, and batches that are executed against the server

• T-SQL_SPs Collects information about individual stored procedures and all Transact-SQL statements initiated within the stored procedure

• SP_Counts Displays stored procedures and the number of times they have been executed

• T-SQL_Replay Collects all Transact-SQL statements that have been executed against the server to allow you to play them later (against the same or some other server)

By default, data is collected on the screen, but it can also be stored in a file or in a database table. The latter two options allow you to preserve the data for future use and further analysis. Storing in a file is particularly useful for replaying the data, while storing in a table is useful for analyzing data using SQL queries.

On the Events Selection tab, you can specify events and data columns to be recorded. By default, only events and columns that are part of the selected template will be displayed:

Some data columns are not applicable for some events, and SQL Server will leave them empty. You can set the Show All Events and/or Show All Columns checkboxes to see the complete matrix. Two panes in the lower-left corner display explanations of the selected event and column. It takes a little time and experimentation to learn which are the most useful. I recommend you analyze some of the templates and see how they are built.

Filters provide you with a way to avoid information overload. For example, you can decide to monitor only those activities performed just by a particular user, only activities with a duration longer than specified, or all activities except those initiated by SQL Profiler in a specific database.

When you have finished modifying the trace properties, you should run the trace. Profiler starts to collect data and display it on the screen (see Figure 16-5).

Figure 16-5: Trace window

When you have gathered enough data, you can pause or stop data collection without closing the window. The top pane displays all specified data columns. In the bottom pane, SQL Profiler displays the complete content of the TextData column for the selected event. In the case of batches and stored procedures in the TextData column, you can find (and copy, for example, to the Query window of Management Studio for further analysis) the command that caused an error or that took too long.

SQL Profiler may consume substantial resources if you just run it without careful planning. For example, gathering too many different events and too many data columns without filtering might reduce the performance of the monitored server; and the trace itself might overwhelm the machine with the amount of data gathered. Some database departments have therefore introduced very rigid limitations on the use of SQL Profiler in production environments. It is unfortunate to lose such a valuable tool, but there are ways to reduce resource contention:

• Do not run SQL Profiler on the server that you are monitoring.

• If the volume of the gathered data is an issue, save it to a file (not to the screen or a database table). Storing trace data in a database table allows you to analyze it with the full power of SQL Server tools, but it might also introduce a performance problem. Ideally, gather data in a file and then periodically load it to a database table.

• To reduce network contention on a busy production server, add a new network connection dedicated to SQL Profiler (in other words, an additional NIC and additional network).

• Use PerfMon to track the impact of SQL Profiler on the production system.

Note 

SQL Server has a set of system stored procedures for creating traces, running them, and collecting data. Their usage might he the preferred solution if you are facing a great quantity of data, or if you want to design a custom application for monitoring SQL Server that introduces new features not covered by SQL Profiler.