Profiling and Monitoring

Article
01/28/2010

Monitoring server performance, tracking user activity, and troubleshooting errors are essential parts of database administration, and Microsoft SQL Server has several tools that you can use to perform these tasks. Performance Monitor, the standard Microsoft Windows 2000/NT tool for monitoring servers, has updated counters for SQL Server. These counters allow you to track many different server resources and activities. SQL Server Profiler, an analysis and profiling tool, allows you to trace server events. Other tools and resources are available, such as stored procedures and the SQL Server logs.

Monitoring Server Performance and Activity

Monitoring SQL Server isn't something you should do haphazardly. You need to have a plan—a set of goals that you hope to achieve. Let's look at some reasons you may want to monitor SQL Server and the tools you can use to do this.

Why Monitor SQL Server?

Troubleshooting SQL Server performance problems is a key reason for monitoring. For example, users may be having problems connecting to the server, and you may want to monitor the server to troubleshoot these problems. Here, your goal would be to track down the problem using the available monitoring resources and then solve it.

Another common reason for wanting to monitor SQL Server is to improve server performance. To achieve optimal performance, you need to minimize the time it takes for users to see the results of queries and maximize the total number of queries that the server can handle simultaneously. You do this by

Resolving hardware issues that may be causing problems. For example, if slow disk drives are delaying queries, work on improving disk input/output (I/O).
Monitoring memory and CPU usage and taking appropriate steps to reduce the load on the server, as necessary. For example, other processes running on the server may be using memory and CPU resources needed by SQL Server.
Cutting down the network traffic load on the server. With replication, for example, configure remote stored procedure execution rather than transmit large data changes individually.

Unfortunately, when it comes to resource usage you often have to make tradeoffs. For example, as the number of users accessing SQL Server grows, you may not be able to reduce the network traffic load, but you may be able to improve server performance by optimizing queries or indexing.

Getting Ready to Monitor

Before you start monitoring SQL Server, you may want to establish baseline performance metrics for your server. To do this, you measure server performance at various times and under different load conditions. You can then compare the baseline performance with subsequent performance to determine how SQL Server is performing. Performance metrics that are well above the baseline measurements may indicate areas where the server needs to be optimized or reconfigured.

After you establish the baseline metrics, you should formulate a monitoring plan. A comprehensive monitoring plan involves the following steps:

Determine which server events should be monitored to help you accomplish your goal.
Set filters to reduce the amount of information collected.
Configure monitors and alerts to watch the events.
Log the event data so that it can be analyzed.
Analyze the event data and replay the data as necessary to find a solution.

These procedures are examined later in this chapter in the section entitled "Monitoring SQL Server Performance." While you should develop a monitoring plan in most cases, sometimes you may not want to go through all these steps to monitor SQL Server. For example, if you want to check current user activity levels, you may not want to use Performance Monitor and may want to run the stored procedure sp_who instead. Or you may simply want to examine this information in the Current Activity window in Enterprise Manager.

Monitoring Tools and Resources

The primary monitoring tools you'll use are Windows Performance Monitor and SQL Server Profiler. Other resources for monitoring SQL Server include

SQL Server error logs Use information in these error logs to troubleshoot SQL Server problems.
SQL Server Agent error logs Use information in these error logs to troubleshoot SQL Server Agent problems.
Event logs Use information in the event logs to troubleshoot system-wide problems, including SQL Server and SQL Server Agent problems.
Enterprise Manager Current Activity window This provides information on current users, processes, and locks.
sp_helpdb This stored procedure displays information about databases.
sp_helpindex This stored procedure reports information about indexes on a table.
sp_helpserver This stored procedure provides information in SQL Server instances configured for remote access or replication.
sp_lock This stored procedure shows information concerning object locks.
sp_monitor This stored procedure shows key SQL Server usage statistics, such as CPU idle time and CPU usage.
sp_spaceused This stored procedure shows an estimate of disk space used by a table or database.
sp_who This stored procedure shows a snapshot of current SQL Server users and processes.
DBCC statements Use this set of commands to check SQL Server statistics, to trace activity, and to check database integrity.

Beyond log files and Transact-SQL statements, you'll find a set of built-in functions that return system information. Table 10-1 provides a summary of key functions and their usages. The values returned by these functions are cumulative from the time SQL Server was last started.

Table 10-1 Built-In Functions for Monitoring SQL Server Performance and Activity

Function	Description	Example
@@connections	Returns the number of connections or attempted connections	select @@connections as 'Total Login Attempts'
@@cpu_busy	Returns CPU processing time in milliseconds for SQL Server activity	select @@cpu_busy as 'CPU Busy,' getdate() as 'Since'
@@idle	Returns SQL Server idle time in milliseconds	select @@idle as 'Idle Time,' getdate() as 'Since'
@@io_busy	Returns I/O processing time in milliseconds	select @@io_busy as 'IO Time,' getdate() as 'Since' for SQL Server
@@pack_received	Returns the number of input packets read from the network by SQL Server	select @@pack_received
@@pack_sent	Returns the number of output packets written to the network by SQL Server	select @@pack_sent
@@packet_errors	Returns the number of network packet errors for SQL Server connections	select @@packet_errors
@@total_errors	Returns the number of disk read/write errors encountered by SQL Server	select @@total_errors as 'Total Errors,' getdate() as 'Since'
@@total_read	Returns the number of disk reads by SQL Server	select @@total_read as 'Reads,' getdate() as 'Since'
@@total_write	Returns the number of disk writes by SQL Server	select @@total_write as 'Writes,' getdate() as 'Since'

Working with the Error Logs

Error logs are your primary resource for tracking SQL Server errors. SQL Server writes events to the SQL Server error logs, the SQL Server agent error logs, and the Windows application log. You can use all three logs to track messages related to SQL Server. However, there are some things you should know:

Some events aren't logged in the application log and you can control the logging of most noncritical events through the Manage SQL Server Messages dialog box in Enterprise Manager. To display this dialog box, choose the Manage SQL Server Messages option from the Tools menu.
Only the application log provides additional information on all applications running on the server, and only the application log provides features for filtering events based on type. For example, you can filter events so that only error and warning messages are displayed.
If you start the MSSQLServer or MSSQL$instancename service from the command prompt, events are logged to the SQL Server error log and to standard output. No events are recorded in the Windows application log.

SQL Server error messages are cryptic and difficult to read if you don't understand the formatting. Messages logged by SQL Server can have

An error number that uniquely identifies the error message System error numbers have one to five digits. System errors are numbered from 1 to 50,000. User-defined errors start at 50,001.
A severity level that indicates how critical the message is Severity levels go from 1 to 25. Messages with a severity level of 10 are informational messages. Severity levels from 11 to 16 are generated by users and users can correct them. Severity levels from 17 to 25 indicate software or hardware errors that you should examine.
An error state number that indicates the source of the error Error state numbers have one to three digits and a maximum value of 127. Normally, error state numbers indicate the line number in the SQL Server code that generated the message.
A message that provides a brief description of the error

ODBC (open database connectivity) and OLE (object linking and embedding) return errors from SQL Server that contain similar information as well.

Examining the Application Log

The application log contains entries for all database server instances running on the computer as well as entries for other business applications. You access the application log by completing the following steps:

Access the Start menu, choose Programs, choose Administrative Tools (Common), and then choose Event Viewer. This starts Event Viewer.
Event Viewer displays logs for the local computer by default. If you want to view logs on a remote computer, right-click the Event Viewer entry in the console tree (left pane) and then select Connect To Another Computer. Afterward, in the Select Computer dialog box, enter the name of the computer you want to access and then click OK.
In the console tree (left pane), click Application Log. You should see the application log shown in Figure 10-1. Use the information in the Source column to determine which service or database server instance logged a particular event.

Figure 10-1: The Windows application log.

The entries in Event Viewer's main window provide a quick overview of when, where, and how an event occurred. To obtain detailed information on an event, double-click its entry. A summary icon that tells you the event type precedes the date and time of the event. Event types include

Informational An informational event that's generally related to a successful action
Success audit An event related to the successful execution of an action
Failure audit An event related to the failed execution of an action
Warning: A noncritical error that provides a warning. Details for warnings are often useful in preventing future system problems.
Critical error A critical error, such as the failure of a service to start

In addition to the date, time, and the event type indicator, the summary and detailed event entries provide the following information:

Source The application, service, or component that logged the event
Category The category of the event, which is sometimes used to further describe the related action
Event An identifier for the specific event
User The user account that was logged on when the event occurred
Computer The computer name where the event occurred
Description In the detailed entries, this provides a text description of the event
Data In the detailed entries, this provides any data or error code output by the event

Warnings and critical errors are the two key types of events that you'll want to examine closely. Whenever these types of events occur and you're unsure of the cause, double-click the entry to view the detailed event description. If you want to see only warnings and errors, you can filter the log by completing the following steps:

From the View menu, choose the Filter option. This opens the dialog box shown in Figure 10-2.
Clear the following check boxes: Information, Success Audit, and Failure Audit.
Select the Warning and Error check boxes.
Click OK. You should now see a list of warning and error messages only. Keep in mind that these messages are for all applications running on the server and not just SQL Server.

Figure 10-2: In the Application Log Properties dialog box, you can filter events so that only warnings and errors are displayed.

Examining the SQL Server Error Logs

The SQL Server logs record information, warnings, errors, and auditing messages pertaining to SQL Server activity. New logs are created when you start the SQL Server service or when you run the sp_cycle_errorlog stored procedure. When a new log is created, the current log is cycled to the archive. SQL Server maintains up to six archived logs.

You can view the SQL Server error logs in Enterprise Manager or through a text editor. In Enterprise Manager, you access the error logs by completing the following steps:

Start Enterprise Manager and then access the database server instance whose logs you want to examine.
Click the plus sign (+) next to the server's Management folder, and then double-click the SQL Server Logs entry.
The current log is shown with the label Current.
Archived logs are shown with the label Archive # N, such as Archive # 1.
To view the properties of an error log entry, double-click it.

To access the error logs in a text editor, complete the steps on the following page.

Start the text editor and then use its Open dialog box to access the SQL Server Log folder that's normally located in mssql\Log or mssql$instancename\Log.
Open the log you want to examine. The current log file is named ERRORLOG with no file extension. The most recent log backup has the extension .1, the second most recent has the extension .2, and so on.

Examining the SQL Server Agent Error Logs

The SQL Server Agent logs record information, warnings, and errors pertaining to SQL Server Agent activity. The only time new logs are created is when you start the SQL Server Agent service. When a new log is created, the current log is cycled to the archive. SQL Server maintains up to six archived logs.

In Enterprise Manager, you access the current SQL Server Agent log by completing the following steps:

Start Enterprise Manager and then access the database server instance whose logs you want to examine.
Click the plus sign (+) next to the server's Management folder. Right-click SQL Server Agent and then select Display Error Log. This displays the SQL Server Agent Error Log dialog box.
Use the Type selection list to choose the type or event to view. The available options are All Types, Error, Warning, and Information.
To view the properties of a log entry, double-click it.

To access archived SQL Server Agent error logs in a text editor, complete the following steps:

Start the text editor and then use its Open dialog box to access the SQL Server Log folder, which is normally located in mssql\Log or mssql$instancename\Log.
Open the log you want to examine. The current log file is named SQLAGENT.OUT. The most recent log backup has the extension .1, the second most recent has the extension .2, and so on.

Monitoring SQL Server Performance

Windows Performance Monitor is the tool of choice for monitoring SQL Server performance. Performance Monitor graphically displays statistics for the set of performance parameters you've selected for display. These performance parameters are referred to as counters.

When you install SQL Server on a system, Performance Monitor is updated with a set of counters for tracking SQL Server performance parameters. These counters can also be updated when you install services and add-ons for SQL Server. For example, when you configure replication on a server, the Replication Monitor window is added to Enterprise Manager, and Performance Monitor is again updated with a set of objects and counters for tracking replication performance.

Performance Monitor creates a graph depicting the various counters you're tracking. You can configure the update interval for this graph, but it's set to 3 seconds by default. As you'll see when you work with Performance Monitor, the tracking information is most valuable when you record the information in a log file and when you configure alerts to send messages when certain events occur or when certain thresholds are reached, such as when a database log file gets close to running out of free space.

The following sections examine key techniques you'll use to work with Performance Monitor.

Note: Performance Monitor isn't available with Windows 95 or Windows 98 desktop installations.

Starting Performance Monitor

You can start Performance Monitor in two ways. You can

Choose the Performance Monitor option from the Administrative Tools menu
In Enterprise Manager, right-click the Replication Monitor entry and then, from the shortcut menu, choose Performance Monitor

The technique you use to start Performance Monitor depends on the tasks you want to accomplish. If you start Performance Monitor from the Administrative Tools menu, no default counters are configured. On the other hand, if you start Performance Monitor from Enterprise Manager, default counters are configured automatically for tracking. These counters show the replication defaults.

Choosing Counters to Monitor

Performance Monitor displays information only for counters you're tracking. Over a hundred SQL Server counters are available—and if you've configured replication, there are even more. These counters are organized into object groupings. For example, all lock-related counters are associated with the MSSQL:Locks object.

To select which counters you want to monitor, complete the following steps:

Start Performance Monitor. As Figure 10-3 shows, any default counters are shown in the lower portion of the Performance Monitor window.
Performance Monitor has several views. Make sure you're in Chart view. Click the View Chart button on the toolbar.
To delete a default counter, click its entry in the lower portion of the Performance Monitor window and then press the Delete key.

Figure 10-3: Counters are listed in the lower portion of the Performance Monitor window.

To add counters, click the Add button on the toolbar. This displays the Add Counters dialog box shown in Figure 10-4. The key fields are
- Use Local Computer Counters Configures performance options for the local computer.
- Select Counters From Computer Enters the UNC (Uniform Naming Convention) name of the SQL Server you want to work with, such as \\ZETA.
- Performance Object Selects the type of object you want to work with, such as MSSQL:Locks.
  
  Note: The easiest way to learn what you can track is to explore the objects and counters available in the Add Counters dialog box. Select an object in the Performance Object field, click Explain, and scroll through the list of counters for this object.
- All Counters Selects all counters for the current object.
- Select Counters From List Selects one or more counters for the current object. For example, you could select Lock Requests/sec, Lock Timeouts/sec, and Number of Deadlocks/sec.
  
  Figure 10-4: In the Add Counters dialog box, select the counters you want to monitor.
- All Instances Selects all counter instances for monitoring.
- Select Instances From List Selects one or more counter instances to monitor. For example, you could select instances of the Lock Requests/sec counter for Database, Extent, and Page.
Tip Don't try to chart too many counters or counter instances at once. You'll make the display too difficult to read and you'll use system resources—namely CPU time and memory—that may affect server responsiveness.
When you've selected all the necessary options, click Add to add the counters to the chart. Repeat this process as necessary to add other performance parameters.
Click Close when you're finished.

Creating and Managing Performance Monitor Logs

You can use performance logs to track the performance of SQL Server, and you can replay them at a later date. As you set out to work with logs, keep in mind that the parameters you track in log files are recorded separately from the parameters you're charting in the Performance Monitor window. You can configure log files to update counter data automatically or manually. With automatic logging, a snapshot of key parameters is recorded at specific time intervals, such as every 10 seconds. With manual logging, you determine when snapshots are made. Two types of performance logs are available:

Counter Logs Record performance data on the selected counters when a predetermined update interval has elapsed
Trace Logs Record performance data whenever their related events occur

Creating and Managing Performance Logging

To create and manage performance logging, complete the following steps:

Access the Performance console by choosing the Performance option from the Administrative Tools menu.
Expand the Performance Logs and Alerts node by clicking the plus sign (+) next to it. If you want to configure a counter log, select Counter Logs. Otherwise select Trace Logs.
As shown in Figure 10-5, you should see a list of current logs (if any) in the right pane. A green log symbol next to the log name indicates logging is active. A red log symbol indicates logging is stopped.
You can create a new log by right-clicking in the right pane and choosing New Log Settings from the shortcut menu. A New Log Settings box appears asking you to give a name to the new log settings. Type a descriptive name here before continuing.

Figure 10-5: Current performance logs are listed with summary information.

To manage an existing log, right-click its entry in the right pane and then select one of the following options:
- Start To activate logging
- Stop To halt logging
- Delete To delete the log
- Properties To display the log properties dialog box

Creating Counter Logs

Counter logs record performance data on the selected counters at a specific sample interval. For example, you could sample performance data for the CPU every 15 minutes. To create a counter log, complete the following steps:

Select Counter Logs in the left pane of the Performance console and then right-click in the right pane to display the shortcut menu. Choose New Log Settings.
In the New Log Settings dialog box, type a name for the log, such as System Performance Monitor or Processor Status Monitor. Click OK.
In the General tab, click Add to display the Select Counters dialog box. This dialog box is identical to the Add Counters dialog box shown previously in Figure 10-4.
Use the Select Counters dialog box to add counters for logging. Click Close when you're finished.
In the Sample Data Every ... field, type in a sample interval and select a time unit in seconds, minutes, hours, or days. The sample interval specifies when new data is collected. For example, if you sample every 15 minutes, the log is updated every 15 minutes.

As shown in Figure 10-6, click the Log Files tab and then, using the following fields, specify how the log file should be created:
- Location Sets the folder location for the log file.
- File Name Sets the name of the log file.
- End File Names With Sets an automatic suffix for each new file that's created when you run the counter log. Logs can have a numeric suffix or a suffix in a specific date format.
- Start Numbering At Sets the first serial number for a log that uses an automatic numeric suffix.
- Log File Type Sets the type of log file to create. Use Text File – CSV for a log file with comma separated entries. Use Text File – TSV for a log file with tab separated entries. Use Binary File to create a binary file that can be read by Performance Monitor. Use Binary Circular File to create a binary file that overwrites old data with new data when the file reaches a specified size limit.
  
  Figure 10-6: Configure the log file format and usage in the Log Files tab.
  
  Tip If you plan to use Performance Monitor to analyze or view the log, use one of the binary file formats.
- Comment Sets an optional description of the log, which is displayed in the Comment column.
- Maximum Limit Sets no predefined limit on the size of the log file.
- Limit Of Sets a specific limit in KB on the size of the log file.
As shown in Figure 10-7, click the Schedule tab and then specify when logging should start and stop.
You can configure the logging to start manually or automatically at a specific date. Select the appropriate option and then specify a start date if necessary.

Tip Log files can grow in size very quickly. If you plan to log data for an extended period, be sure to place the log file on a drive with lots of free space. Remember, the more frequently you update the log file, the higher the drive space and CPU resource usage on the system.

You can configure the log file to stop
- Manually
- After a specified period of time, such as seven days
  
  Figure 10-7: Use the Schedule tab to specify when logging starts and stops.
- At a specific date and time
- When the log file is full (if you've set a specific file size limit)
Click OK when you've finished setting the logging schedule. The log is then created and you can manage it as explained in the "Creating and Managing Performance Logging" section of this chapter.

Creating Trace Logs

Trace logs record performance data whenever events for their source providers occur. A source provider is an application or operating system service that has traceable events. On domain controllers you'll find two source providers: the operating system itself and Active Directory:NetLogon. On other servers the operating system will probably be the only provider available.

To create a trace log, complete the following steps:

Select Trace Logs in the left pane of the Performance console and then right-click in the right pane to display the shortcut menu. Choose New and then select New Log Settings.
In the New Log Settings dialog box, type a name for the log, such as Database Locks Trace or SQL Server Trace. Then click OK. This opens the dialog box shown in Figure 10-8.
If you want to trace operating system events, select the Events Logged By System Provider option button. As shown in Figure 10-8, you can now select system events to trace.

Figure 10-8: Use the General tab to select the provider to use in the trace.

Caution: Collecting page faults and file detail events puts a heavy load on the server and causes the log file to grow rapidly. Because of this, you should collect page faults and file detail events only for a limited time.
If you want to trace another provider, select the Nonsystem Providers option button and then click Add. This displays the Add Nonsystem Providers dialog box, which you'll use to select the provider to trace.

When you're finished selecting providers and events to trace, click the Log Files tab. You can now configure the trace file as detailed in step 6 of the section of this chapter entitled "Creating Counter Logs." The only change is that the log file types are different. With trace logs, you have two log types:
- Sequential Trace File Writes events to the trace log sequentially up to the maximum file size (if any)
- Circular Trace File Overwrites old data with new data when the file reaches a specified size limit
Choose the Schedule tab and then specify when tracing starts and stops.
You can configure the logging to start manually or automatically at a specific date. Select the appropriate option and then specify a start date if necessary.
You can configure the log file to stop manually, after a specified period of time, (such as seven days), at a specific date and time, or when the log file is full (if you've set a file size limit).
When you've finished setting the logging schedule, click OK. The log is then created, and you can manage it as explained in the section of this chapter entitled "Creating and Managing Performance Logging."

Replaying Performance Logs

When you're troubleshooting problems, you'll often want to log performance data over an extended period of time and analyze the data later. To do this, complete the following steps:

Configure automatic logging as described in the "Creating and Managing Performance Monitor Logs" section of this chapter.
When you're ready to analyze the data, load the log file in Performance Monitor. To do this, select the View Log File Data button on the Performance Monitor toolbar. This displays the Select Log File dialog box.
Use the Look In selection list to access the log directory, and then select the log you want to view. Click Open.
Counters you've logged are available for charting. Click Add on the toolbar and then select the counters you want to display.

Configuring Alerts for Performance Counters

You can configure alerts to notify you when certain events occur or when certain performance thresholds are reached. You can send these alerts as network messages and as events that are logged in the application event log. You can also configure alerts to start applications and performance logs.

To add alerts in Performance Monitor, complete the following steps:

Select Alerts in the left pane of the Performance console, and then right-click in the right pane to display the shortcut menu. Choose New Alert Settings.
In the New Alert Settings dialog box, type a name for the alert, such as Database Alert or SQL Server Locks Alert. Then click OK. This opens the dialog box shown in Figure 10-9.
In the General tab, type an optional description of the alert in the Comments field. Then click Add to display the Select Counters To Log dialog box. This dialog box is identical to the Add Counters dialog box shown previously in Figure 10-4.
Use the Select Counters To Log dialog box to add counters that trigger the alert. Click Close when you're finished.
In the Counters panel, select the first counter and then use the Alert When Value Is ... field to set the occasion when an alert for this counter is triggered. Alerts can be triggered when the counter is over or under a specific value. Select Over or Under, and then set the trigger value. The unit of measurement is whatever makes sense for the currently selected counter(s). For example, to alert if processor time is over 95 percent, you would select Over and then type 95. Repeat this process to configure all counters you've selected.

Figure 10-9: Use the SQL Server Alert dialog box to configure counters that trigger alerts.
In the Sample Data Every ... field, type in a sample interval and select a time unit in seconds, minutes, hours, or days. The sample interval specifies when new data is collected. For example, if you sample every 10 minutes, the log is updated every 10 minutes.

Caution: Don't sample too frequently. You'll use system resources, and you may cause the server to become slow in responding to user requests.

Click the Action tab as shown in Figure 10-10. You can now specify any of the following actions to be taken when an alert is triggered:
- Log An Entry In The Application Event Log Creates log entries for alerts
- Send A Network Message To Sends a network message to the computer specified
- Start Performance Data Log Sets a counter log to start when an alert occurs
- Run This Program Sets the complete file path of a program or script to run when the alert occurs
Figure 10-10: Use the Action tab to set actions that are executed when the alert occurs.

Tip You can run any type of executable file, including batch scripts with the .bat or .cmd extension and Windows scripts with the .vb, .js, .pl, or .wsc extension. To pass arguments to a script or application, use the options of the Command Line Arguments panel. Normally, arguments are passed as individual strings. However, if you select Single Argument String, the arguments are passed in a comma-separated list within a single string. The Sample Arguments List at the bottom of the panel shows how the arguments would be passed.
Click the Schedule tab and then specify when you want alerting to start and stop. For example, you could configure the alerts to start on Friday evening and stop on Monday morning. Then each time an alert occurs during this period, the specified action(s) are executed.
You can configure alerts to start manually or automatically at a specific date. Select the appropriate option and then specify a start date if necessary.
You can configure alerts to stop manually, after a specified period of time (such as seven days), or at a specific date and time.
When you've finished setting the alert schedule, click OK. The alert is then created and you can manage it in much the same way that you manage counter and trace logs.

Solving Performance Problems with Profiler

Whether you're trying to track user activity, troubleshoot connection problems, or optimize SQL Server, SQL Server Profiler is one of the best utilities available. Profiler enables you to trace events that occur in SQL Server. Events you can track in Profiler are similar to counters you can monitor in Performance Monitor. They're organized into groups called event classes, and you can track one or more events for any of the available event classes. Profiler's strength is in its advanced features and extensive customization capabilities.

You can record and replay Profiler traces when you want to analyze the data— and this is one area where Profiler excels. You can

Use the information to find slow-running queries and then determine what's causing the queries to run slowly
Go through statements a step at a time to find the cause of a problem
Track a series of statements that cause a particular problem and then replay the trace on a test server to determine the cause
Use trace information to determine the cause of deadlocks
Monitor user and application activity to determine actions that are using CPU time or queries that are taking a long time to process

Let's look at how you can work with Profiler. Afterward I'll cover creating and managing traces.

Using Profiler

You can start Profiler in two ways. You can

Choose the Profiler option from the Microsoft SQL Server menu
In Enterprise Manager, from the Tools menu, choose SQL Server Profiler

Figure 10-11 shows Profiler in the process of running a trace. The columns shown for the trace, such as Event Class and Event SubClass, are completely configurable when you're setting up the trace, allowing you to select or clear columns as necessary. Two columns you'll want to pay particular attention to are Duration and CPU. The Duration column shows how long a particular event has been running in milliseconds. The CPU column shows the event's processing time by the CPU in milliseconds.

Stored procedures provide an alternative to Profiler. Using these stored procedures gives you some options that you don't have with SQL Server Profiler. You can

Store traces in the Windows application log
Autostart a trace when SQL Server starts
Forward event data to another computer running SQL Server (Windows NT or Windows 2000 only)

Figure 10-11: Use the SQL Server Profiler dialog box to create traces of SQL Server events.

To create traces with stored procedures, complete the following steps:

Create a trace definition using sp_trace_create.
Set events to capture using sp_trace_setevent.
Set event filters using sp_trace_setfilter.

Creating New Traces

You use traces to record events generated by local and remote SQL servers. You run traces in the Profiler window and store them for later analysis.

To start a new trace, complete the following steps:

Start SQL Server Profiler and then click the New Trace button. Or select File, then New, and then Trace. You'll see the Connect To SQL Server dialog box.
Select the SQL Server instance that you want to trace and then configure the authentication technique to use for the connection. Afterward, click OK.
You'll see the Trace Properties dialog box, as shown in Figure 10-12.
In the Trace Name field, type a name for the trace, such as Data Trace or Deadlock Trace For CustomerDB.
The Profiler window displays traces automatically. You can store traces as they are being created by setting the Save To File or the Save To Table option, or both. Or you can store a running trace later by selecting File, then selecting Save As, and then choosing either the Trace File option or the Trace Table option.

Figure 10-12: Configure the trace using the tabs and fields in the Trace Properties dialog box.

Tip As you might imagine, there are advantages and disadvantages to using trace files and trace tables. With trace files, you can store traces quickly and efficiently while using minimal system resources. With trace tables, you get ease of use by being able to store a trace directly in a table on another server, but you use much more system resources and usually have slower response times. Note also that storing a trace only saves the trace data. It doesn't save the trace definition. To reuse the trace definition, you'll have to export the trace definition.
SQL Profiler templates are used to save trace definitions that contain the events, data columns, and filters used in a trace. Use the Template Name selection list to choose a template to use as the basis of the trace. If you don't see the template you want to use, click the folder button to the right of the Template File Name field and then use the Open File dialog box to find a different template file. SQL Profiler templates end with the .tdf file extension.
Click the Events tab, as shown in Figure 10-13. Over 75 individual events are available. Available Event Classes lists all events available to be traced. Selected Event Classes lists all events selected to be traced. The best way to learn the types of events you can trace is to select each event or event class and read its description in the lower portion of the Events tab.

Figure 10-13: Use the Events tab to select event classes or individual events to add to the trace.
Select events to add to the trace. Use the Add button to add event classes or individual events to a trace definition. Use the Remove button to remove event classes or individual events from a trace definition. Remember, the more events you trace, the more events will be generated on the source server, which can use up system resources and bog down the trace queue.
Click the Data Columns tab, as shown in Figure 10-14. Then select data columns to collect in the trace. The data columns you select determine the columns of information displayed with the trace. For example, if you select the ApplicationName column, you'll see the corresponding column in the trace data.

Tip If you're tracking distributed queries, be sure to add the HostName column that corresponds to the ServerName in the display window. For transactions, be sure to add the TransactionID column. Also, if you plan to replay the trace for troubleshooting, look in the section of this chapter entitled "Replaying Traces" for specific event classes and data columns that you need to select.

Figure 10-14: Use the Data Columns tab to choose data columns to display for traced events. The default settings are usually fine.

To focus the trace on specific types of data, you may want to set criteria that exclude certain types of events. If so, click the Filters tab and then set filter criteria. For each event category, you can use different filtering criteria. You use these criteria as follows:
- Equals, Not Equal To, Greater Than Or Equal, or Less Than Or Equal Set the values that trigger the event. Events with values outside the specified range are excluded. For example, with the CPU event category you can specify that only events that use greater than or equal to 1000 milliseconds of CPU time are captured. If events use less CPU time than this, they're excluded.
- Like or Not Like Enter strings to include or exclude for this event category. Use the wildcard character (%) to match a series of characters. Use the semicolon (;) to separate multiple strings. For example, with the Application Name category, you could exclude all application names that start with MS and SQL Server by typing MS%;SQL Server%.
When you're finished configuring the trace, click OK to create the trace.

Working with Traces

Profiler displays information for multiple traces in separate windows that can be cascaded or tiled. Use the buttons on the Profiler toolbar to control the active trace. Using the buttons on the Profiler toolbar, you can

Create a new trace by clicking the New Trace button and then configuring the trace using the New Trace dialog box.
Create a trace template by clicking New Template, setting trace properties, and then clicking Save.
Start the current trace by clicking the Start Selected Trace button.
Pause the current trace by clicking the Pause Selected Trace button. You can then use the Start Selected Trace button to resume the trace where it left off.
Stop the current trace by clicking the Stop Selected Trace button. If you start the trace again with the Start Selected Trace button, the Profiler display starts over from the beginning of the trace process; new data is appended to the files or tables to which you're capturing data.
Edit trace properties by clicking the Properties button.

Saving a Trace

When you create traces in Profiler, you create trace data and trace definitions. The Profiler window displays trace data, and you can also store it in a file or a table, or both. The trace data records a history of events that you're tracking, and you can use this history to replay the events for later analysis. The Trace Properties dialog box displays the trace definition. You can use the trace definition to create a new trace based on the existing trace.

To save trace data, complete the following steps:

Access the Profiler window that displays the trace you want to save.
Select File, point to Save As, and then select Trace File or Trace Table.
Use the Save As dialog box to select a folder location. Type the filename and then click Save. Trace files end with the .trc extension.

To save a trace definition, complete the following steps:

Access the Profiler window that displays the trace whose definition you want to save.
Select File, point to Save As, and then select Trace Template.
Use the Save As dialog box to select a folder location. Type the filename and then click Save. Trace templates end with the .tdf extension.

Replaying Traces

One of the main reasons for creating traces is that it gives you the ability to save traces and replay them later. When replaying traces, Profiler can simulate user connections and authentication, which allows you to reproduce the activity recorded in the trace. To aid in troubleshooting, you can

Execute traces step by step to closely monitor each step in the trace
Execute traces using the original timeline to simulate user loads
Execute traces with a high replay rate to stress test servers

As you monitor the trace execution, you can look for problem areas. Then, when you identify the cause of problems you're trying to solve, you can correct them and then rerun the original trace definition. If you're still having problems, you'll need to reanalyze the trace data or look at other areas that may be causing problems. Keep in mind that you may need to specify different events to capture in the subsequent trace.

Requirements for Replaying Traces

Traces that you want to replay must contain a minimum set of events and data columns. If the trace doesn't contain the necessary elements, you won't be able to replay the trace. The required elements are in addition to any other elements that you want to monitor or display with traces. Events that you must capture in order to allow a trace to be replayed and analyzed correctly are

Connect
CursorExecute (required only when replaying server-side cursors)
CursorOpen (required only when replaying server-side cursors)
CursorPrepare (required only when replaying server-side cursors)
Disconnect
Exec Prepared SQL (required only when replaying server-side prepared SQL statements)
ExistingConnection
Prepare SQL (required only when replaying server-side prepared SQL statements)
RPC:OutputParameter
RPC:Starting
SQL:BatchStarting

Data columns that you must capture to allow a trace to be replayed and analyzed correctly are

Application Name
Binary Data
Connection ID or SPID
Database ID
Event Class
Event SubClass
Host Name
Integer Data
Server Name
SQL User Name
Start Time
Text

Replaying Traces on a Different Server

You can replay a trace on a server other than the server originally traced. This server is called the target system. When replaying traces on the target, you should ensure that all logins contained in the trace

Are created on the target system and are in the same database as the source system
Have the same permissions they had originally
Have the same passwords they had originally
Are set to use a default database that matches the database on the source system

If these settings aren't the same, you'll see errors, but the replay operation will continue. Also, database IDs on the target system must be the same as those on the source system. The easiest way to set up databases on the target is to complete the following steps:

Back up the master database on the source and any user databases used in the trace.
Restore the databases on the target as explained in the section of Chapter 11 entitled "Restoring a Database to a Different Location."

Replaying and Analyzing a Trace

Replaying a trace allows you to analyze problems. To get started, start Profiler and then select the Open Trace File or Open Trace Table button, as appropriate for the type of trace you want to replay. After you select the trace to replay, the trace is then loaded into the Profiler window. As Figure 10-15 shows, events and commands recorded in the trace are summarized in the Profiler window. You can select an entry to see an expanded list of commands executed.

As Figure 10-15 also shows, the window toolbar for replay is different from the standard toolbar. The buttons provide just about everything that you need to debug traces, including

Start Replay Starts executing the trace
Pause Replay Pauses execution of the trace
Stop Replay Stops execution of the trace

Figure 10-15: The Profiler window displays all the commands executed and the events collected in the trace.
Execute One Step Allows you to move through the trace one step at a time
Run To Cursor Allows you to move through the trace using cursor sets
Toggle Breakpoint Allows you to set breakpoints for the trace execution

When you start the replay, you'll get an initial dialog box that asks you to configure replay options (see Figure 10-16). You configure the options in the Replay SQL Server dialog box to control where and how the playback takes place. Start by setting the destination server for the replay operation. Then set replay options.

The replay options determine how closely the replay mirrors the original event execution. You can choose

Replay Events In The Order They Were Traced Events are started in the order in which they originally started. This enables debugging but doesn't guarantee timing of event execution. Events may be executed sooner than their original start time or after their original start time, depending on current activity levels, the current speed of connections, and other factors.
Replay Events Using Multiple Threads Events are replayed as quickly as they can be processed. No timing is maintained between events. When one event completes, the next event is started. This optimizes performance and disables debugging.

Figure 10-16: Replay options in the Replay SQL Server dialog box allow you to control where and how the playback takes place.

The Display Replay Results check box controls whether the replay results are displayed in the Profiler window. To display results, select this option. Otherwise, clear this option.

You can also select an output file to save the result of the replay for later viewing. The output file allows you to review the replay just as you would any other trace file.

Click to order