Monitor Activity with the Profiler

• Monitor and troubleshoot database activity by using SQL Profiler.

The SQL Profiler tool is a graphical mechanism that enables you to monitor SQL Server events. The tool enables you to capture and save data about every event on the server. The data can be stored to a file or SQL Server table. Stored data can then be analyzed and events can be replayed.

NOTE

A Monitoring Machine Don't run the SQL Server 2000 Profiler on the same server you are monitoring. Running the Profiler uses considerable resources and this can noticeably affect the server's performance. Instead, run it on another server or workstation acting as a monitoring machine and have all data collected there.

The SQL Profiler should be used to monitor only the events of interest. Monitoring all events on the server produces so much data that the data can become overwhelming. Large traces can be filtered so that you are viewing only the information you want to see. You can use filters to view a subset of the event data that was captured. Monitoring too many events also adds excessive amounts of overhead to the server. This overhead can slow the monitoring process and cause the large amounts of output from the trace to create a file or table that is very large. This is particularly important when you are going to be performing the monitoring over long periods.

SQL Profiler is a useful tool for a number of circumstances. Use SQL Profiler to:

• Monitor the performance of SQL Server.

• Debug T-SQL statements and stored procedures.

• Identify long-running queries.

• Step through procedures to ensure that they are working as expected.

• Capture events on a production system and replay them on a test system.

• Diagnose problem situations through the capturing of event data.

• Audit and review events.

In troubleshooting the SQL Server environment, you will typically use the SQL Profiler. The tool is best used to find queries that are not performing well or ones that are executing for long periods of time. It is also useful in identifying the cause of data blocking and deadlock situations. In monitoring a healthy server, the SQL Profiler is generally used to monitor performance and to audit application, user , database, and job activity.

Before you start using the Profiler, you should become familiar with profile templates. A template defines the criteria for each event you want to monitor with SQL Profiler. Predefined templates can be used for individual circumstances, and you can create your own template, as well, specifying which events, data columns , and filters to use. A template can be saved, and at any time a trace can be loaded and started with the template settings. To help identify long-running queries, use the Profiler's Create Trace Wizard to run the "TSQL by Duration" template. You can specify the length of the long-running queries you are trying to identify, and then have these recorded in a log.

The SQL Profiler captures data using a trace based upon the selected events, data columns, and filters. The trace is the basis for all data collected and can be defined on an ad hoc basis, drawn from a template, or a combination of the two. Even though you have defined the data collected, you may still apply filters to the data after it is captured to focus in on the type of information you want. For this reason you may want to save traces even after you are finished with the current activity. A past trace can possibly be used and applied to a number of circumstances.

At times, when monitoring with the Profiler, you will find the amount of data provided to be considerable and possibly overwhelming. It can be difficult to find what you are looking for within a trace that covers a broad range of events. A useful technique that can ease this process is to write the trace to a SQL Server table (see Step by Step 12.4), and then query the table from within the Query Analyzer. Assuming you know what you are looking for, this method can greatly speed up finding the data in the trace you need.

The Profiler can store captured data in a text file or in a table. If you decide to store the data in a SQL Server table, don't store it in a database you are profiling or, if possible, not even on the same server, because it could affect the performance of the server you are profiling. Instead, store the trace data in a database on another server.

After data is captured (which is the easy part of the process), you must sift through the data collected to draw some meaning from the results.

STEP BY STEP

12.4 Creating a Profile Trace

1. Start the SQL Server Profiler from the Start, Programs, Microsoft SQL Server shortcut menu.

2. From the File menu, select New Trace and provide connection information if necessary.

3. In the Trace Properties dialog box, provide a name for the trace, and select SQLProfilerStandard from the Template Name drop-down list box, as displayed in Figure 12.3.

   Figure 12.3. Trace Properties dialog box.

   

4. Select the Save to File option and select a location in which to store your sample file. (The file can be stored in any location that has sufficient free space. There must always be at least 10MB of free space for storage for the Profiler to run, regardless of any size limits set.)

5. You can view the settings of this template by selecting the Events, Data Columns, and Filters tabs on the dialog box. Leave the remaining entries at their default settings and click Run.

6. The trace begins to execute and collect data to your storage file. After a few seconds (1520) stop the trace by selecting the Stop button at the top of the Trace window.

7. Notice that the default template, SQLProfilerStandard , gathered information on Existing Sessions, Stored Procedures, T-SQL Statements Completing, and Audit Logins/Logouts.

8. Close/Exit the Profiler window.

The Profiler can use many existing templates to gather information for a variety of types of circumstances. You may want to select some of these templates to see the actual information that is being gathered. After it has been created, the trace is permanently stored until it is deleted. The trace can be started again by name through the Profiler interface or via stored procedure.

To use the output generated by a trace, you must first determine what type of data you are most interested in from the trace. The next section illustrates how to get the most valuable information from the trace results.

Defining a Profiler Trace

When using the SQL Profiler to define a trace, you use event categories to select the events to monitor. Event categories have been grouped into classes of events. The following list of classes and their descriptions are available to be selected:

• Cursors . Events produced by use of cursors.

• Database. Events produced when data or log files grow or shrink automatically.

• Errors and Warnings. Events produced when a SQL Server error or warning occurs.

• Locks. Events produced when a lock is acquired , cancelled, released, or other lock activity.

• Objects. Events produced when database objects are created, opened, closed, dropped, or deleted.

• Performance. Events produced when SQL data manipulation operators execute.

• Scans. Events produced when tables and indexes are scanned.

• Security Audit. Events used to audit server activity.

• Sessions. Events produced by clients connecting and disconnecting.

• Stored Procedures. Events produced by the execution of stored procedures.

• Transactions. Events produced by the execution of Microsoft Distributed Transaction Coordinator transactions or by writing to the transaction log.

• TSQL. Events produced by the execution of T-SQL statements.

• User Configurable. User-configurable events.

Each event class has a number of different objects that can be monitored . To select any of the objects when defining a trace, use the Events tab of the Trace Properties dialog box as shown in Figure 12.4. You add and remove objects, not whole classes, although a whole class of objects can be traced, if desired.

Figure 12.4. Trace Properties.

EXAM TIP

Event Classes On the exam, you may be expected to select the correct event classes and objects given a specific scenario. Ensure that you are familiar with the classifications of the Profiler trace objects and under what circumstances you would select them. By viewing the objects selected for each of the default templates, you can familiarize yourself with these choices.

After the objects have been selected, two steps still remain to complete the trace definition: selection of data columns for the trace report and specification of filters to select appropriate data.

The Data Columns tab enables you to select the groupings and columns that will represent how a particular trace report is visually presented. Select the groups of objects that will best suit your needs when the time comes to decipher the output. The data columns selected will be the columns of the report grouped in the manner selected by Groups. A sample grouping and column list definition is illustrated in Figure 12.5.

Figure 12.5. Trace group and column definition.

Grouping by database and object names may not be the desired format for all situations. For example, you may want to group the report by duration to more easily find the statements that execute for the longest time.

The final step in the process of defining a trace is to filter out desired information or set up the filters to focus on only particular information. There are really two approaches to setting filters:

• See all information except particular types.

• See only information of a particular type.

Filters can be set to ignore information or to specifically look at information of only a particular nature. By setting filters you can determine which database(s) to capture, which application, whether system information is to be included in the trace, and so on. Figure 12.6 shows a filter setting that will not show information for the Pubs database.

Figure 12.6. Filtering out the Pubs database.

After it has been defined, the trace execution gathers results based on the criteria you have selected. Depending on the amount of data that has been collected, you will be adding some overhead to the machine where the trace is executing. It is best, therefore, to select the time you are going to run the trace, and don't allow it to run longer than necessary. Also, if you are replaying the trace you should have a test server available for that purpose.

Viewing the results, then, should bring light to the issues surrounding the reason you decided to trap the data in the first place.

Profiler Traces to Diagnose Locking

SQL Profiler provides the Locks event classes to monitor locking behavior during trace operations. Several of these classes are useful in monitoring locking, blocking, and deadlocking situations on the server. The following list represents the classes with a short narrative description of what each class can be used for:

• Lock:Acquired . Shows the acquisition of a resource lock.

• Lock:Cancel . An event is fired when a lock on a resource has been cancelled. A lock can be cancelled by SQL Server because of a deadlock or by a programmatic cancellation by a process.

• Lock:Deadlock . A deadlock occurs if two concurrent transactions have deadlocked each other by trying to obtain locks on resources that the other owns.

• Lock:Deadlock Chain . The chain of events produced for each of the processes leading up to a deadlock situation.

• Lock:Escalation . An event fired when the server determines that a lock should be converted to a larger scope.

• Lock:Released . The event fires when a resource lock is released.

• Lock:Timeout . Fires when a lock request has timed out because another process is blocking a resource with its own lock.

Lock:Acquired and Lock:Released are used to monitor the timing of lock behavior. These events indicate the type of lock and the length of time the lock was held. Often a redesign of the application that is setting the locks in place can lessen the lock duration considerably.

The Lock:Deadlock , Lock:Deadlock Chain , and Lock:Timeout are used to monitor deadlock and timeout situations. This information is useful to determine whether deadlocks and timeouts are affecting the user and/or application.

Using Profiler Results

The Results window of the SQL Profiler is segmented into two view panes. If you have included TextData as one of the columns in your definition, then the bottom pane shows you SQL statement information. The top pane illustrates the current trace data view where event information is displayed based on current filter settings. A sample of trace output in Figure 12.7 illustrates the events from a simple query in the captured results.

Figure 12.7. Sample of trace output in the Profiler window.

After trace event data has been collected, you can save the trace to have it replayed later. The SQL Profiler Playback feature is powerful but carries a little overhead. It is well worth considering having a test machine available to act as a playback and troubleshooting server.

Playback of events is accomplished through the SQL Server multithreaded playback engine. This engine can simulate user connections and SQL Server authentication. The event data can be played back to reproduce the activity captured in the trace. Replay can be very useful in troubleshooting an application or other process problem.