Using SQL Profiler

The SQL Profiler in SQL Server 7.0 is powerful and easy to use. SQL Profiler is based on the SQL Server 6.x SQL Trace utility (for details on this utility, see the sidebar "An Overview of SQL Trace"). This article explains how you can use SQL Profiler to monitor, analyze, and tune SQL Server. I cover the differences in functionality between SQL Trace and SQL Profiler and how to use the Trace Wizard.

The revised architecture of SQL Server 7.0 features an integrated event model, in which various subcomponents of SQL Server (the query processor, Open Data Services—ODS, log manager, lock manager, the error log, etc.) act as event producers, initiating events when specific activities occur. An event controller centrally manages these events. Event consumers handle these events in various ways. SQL Profiler can act as an event consumer, reporting on these events and giving detailed server activity information. (For more information about unique terminology, see "SQL Profiler Terms".) For example, SQL Profiler can trace about 60 engine events and can define more. Here are some basic exercises you can do to become familiar with SQL Profiler's functionality.

Getting Started with SQL Profiler

After you start SQL Profiler, you need to register the SQL Server you want to profile. You need to connect to SQL Server as a member of the Sys Admin role. You can either use the credentials of the NT account you're logged in as (Windows NT authentication) or supply a valid login name and password for the server you're connecting to (SQL Server authentication). NT authentication means one less password to remember and allows neat NT service-based application access without either hard coding a password or leaving it lying around somewhere. If you choose SQL Server authentication, enable Always prompt for username and password. Screen 1 shows the dialog box for connecting to the server.

After you register the SQL Server, you can run a sample trace supplied with SQL Profiler, use the Create Trace Wizard, or create a new trace. You can study these options to choose the best one for your purpose. SQL Profiler comes with several sample trace files that you can inspect, edit or use as-is to generate meaningful performance and activity information.

Each trace that you execute contains information on what you're trying to profile, how you want the results presented, and what data you want the trace to include. This trace information—events, data columns, and filters—describes what a SQL Profiler trace captures. Events are incidents that cause SQL Profiler to display an entry. Data columns determine what the trace stores in an entry. Filters determine the criteria (other than event occurrence) that an event must meet before SQL Profiler makes an entry. (Refer to SQL Server Books Online—BOL—for an extended description of event groups, events, etc.). In the samples in this article, I've used only the filter that prevents the display of SQL Profiler activity. Usually, the only activity you won't be interested in when you profile a SQL Server is the information that the act of profiling generates. (However, you might want to know whether other people are profiling a server.) You can execute the following sample traces for a better understanding of SQL Profiler.

Sample 1: Transact SQL (T-SQL)

This sample trace will generate information that's familiar to SQL Trace users. First, open the Trace definition for Sample 1 – T-SQL. (On the SQL Profiler menu, select File, Open, Trace Definition; or, in the Trace Name drop-down list, select the Edit Trace Properties button, then select Sample 1 – TSL.) This basic trace doesn't show any information that a SQL Server 6.5 DBA couldn't get with SQL Trace, but its minimal information makes this basic trace easy to experiment with.

This sample trace generates information on definition, events, session (Connect, Disconnect, and ExistingConnection), T-SQL (RPC:Starting and SQL:BatchStarting), data columns, application name, text, event class, and start time. This trace captures events within two event classes (Session and T-SQL). The three session events capture all SQL Server connections and disconnections, including current connections. The two T-SQL events (RPC:Starting and RPC:BatchStarting) capture SQL calls sent to the server as a set of SQL statements (an SQL batch) or a remote procedure call (RPC).

Sample 2: T-SQL (Grouped)

To display data in a useful format, you can use a GROUP BY function with SQL Profiler to separate data into groups by, for example, application name, NT user name, SQL Server user name, or ConnectionID. In contrast, SQL Trace lets you trace only by individual connection, which SQL Trace displays in a separate window. This SQL Trace function is useful, but it doesn't let you split a user's activity among several windows when an application has more than one connection. And SQL Trace won't accommodate other ways of separating activity, such as by application or by user.

Sample 3: Stored Procedure Counts

This simple trace captures only one event, SP:Starting. However, it lets you generate a simple profile of how often users call a specific stored procedure so you can apply relevant performance improvements.

Sample 4: T-SQL + Stored Procedure Steps

This sample demonstrates one of the most useful and exciting features of profiling with SQL Server 7.0. You can use this sample to debug any large stored procedure that contains multiple paths. You can examine which SQL statement within a stored procedure is executed to trap logic flaws or unanticipated conditions. In addition, you can use this filter to investigate performance variation in a given stored procedure. Using SQL Profiler to debug stored procedures is a big improvement over previous options. For example, if an execution-path problem occurs one time in 10,000 in a large stored procedure with several possible execution paths, you could use SQL Server integrated debugging to correct the problem. However, this method is time-consuming and intrusive to other system users. Or you could wade through the stored procedure to determine the problem. This option, too, is time-consuming. But with SQL Profiler, you can run the stored procedure with a trace active to analyze the problem. After you verify the incorrect behavior, you can examine the trace for a specific time, user, and workstation to obtain a set of SQL statements running within this stored procedure. The definition of this trace is similar to that of sample three, with the addition of SP:stmtstarting, which indicates that a statement within a stored procedure is starting.

Sample 5: T-SQL by Duration

Useful for performance purposes, this sample provides valuable timing information for T-SQL statements. This sample is the basis for the wizard-generated trace for slow-running queries outlined later in this article.

Sample 6: T-SQL for Replay

This interesting sample lets you capture T-SQL events for replay. This feature in SQL 7.0 is powerful for performance tuning and optimization, debugging, or tinkering.

The six samples that SQL Profiler provides demonstrate only about one-tenth of its capabilities, but even these six sample traces give you greater power and flexibility in troubleshooting and performance tuning than SQL Trace does. You can copy and modify these traces (I recommend that you preserve the sample traces as templates) to handle commonly asked questions such as what a stored procedure is doing, why a query is taking so long, and what is being called.

One function that these samples don't cover is lock monitoring. Although you can do lock monitoring in SQL 6.5 with trace flags, you can do it more easily in SQL 7.0 with SQL Profiler.

Using the Create Trace Wizard

The Create Trace Wizard features six common scenarios: finding the worst-performing queries, identifying scans of large tables, identifying the cause of a deadlock, profiling stored procedure performance, tracing T-SQL activity by application, and tracing T-SQL activity by user. You can modify one of the sample filters to easily create the last two scenarios.

To create a trace, select Tools, Create Trace Wizard. An information screen will tell you that the steps involved in using the Create Trace Wizard are identifying the problem, specifying the filter conditions, and completing the trace definition. The next screen will ask you to select a server and one of the predefined scenarios. The next screen will ask you to complete a scenario-specific filter definition section.

Table 1 shows three wizard-generated sample traces. You can use these traces as they are or use the Trace Properties dialog box to modify them. To modify an existing trace or to create a new trace, you need to become familiar with the Trace Properties box. You can choose File, then Properties or use the Trace Properties button to open the dialog box. Screen 2 shows the General tab of this dialog box. SQL Trace users will recognize most of the options that this dialog box presents. The Trace Type (Shared or Private) determines who can use the specified trace. The trace type determines where the definition is stored—on the SQL Server Registry in LocalMachine (Shared) or on the local machine under CurrentUser (Private). You can choose the location. If you want to use Shared traces, you need to modify the server registry. But Private traces have no such requirement. You can use the Import/Export trace options to transfer these traces as trace-definition files. The Capture to Table option puts trace output in a SQL Server table for later analysis or replay. You can specify the table's server, database, owner, and name.

Screen 3 shows the SQL Profiler Events tab, which has many options not available with SQL Trace. The tab lists events by event group, and you can add events individually or by class. BOL documents event classes and events. You won't need all events in all situations, and both the sample traces and the Trace Wizard-generated traces have a limited subset of these events. I recommend you include a limited subset of these events in any traces you define. You can double-click an event group to add or remove it. In addition, you can choose Tools, Options to display all event classes (data columns) or only commonly used events.

Screen 4 shows the Data Columns tab. If you select columns carefully, SQL Server will generate meaningful summary data. The Groups facility lets you choose data analysis by application, type of operation, etc. The available data columns depend on the events you selected on the previous tab. Refer to BOL for a full list of data columns and where they are relevant.

Screen 5 shows the Filters tab. If you don't set a filter, then SQL Server will return all requested events. If you set a filter, SQL Server will return only events that also meet the criteria you specified. You can choose from three types of filters. Name filters include and exclude specified textual values. Range filters can accept a maximum and minimum value for reporting. ID filters screen for specified IDs. Note that you can specify only one value for ID filters. For specific examples of creating a trace, see the sidebar "Creating a Trace."

More Power, More Options

SQL Profiler in SQL Server 7.0 is powerful and easy to use. Its wizards and samples give you a jump start for gathering useful data about server performance.