EXPERT

SQL SERVICE

Use the SQL Server Profiler to analyze your database and improve performance.

by Mary V. Hooke

Isolating bottlenecks is half the battle when you're working to optimize your database's performance. The SQL Server Profiler can help you monitor and analyze your database's performance to isolate problem areas. I'll provide an overview of the profiler and explain how to use it to analyze your database activity.

The SQL Server Profiler is a tool that allows you to record specific database activity such as queries, transactions, cursors, locks, and stored procedures. Introduced in SQL Server 7.0, the profiler replaces the SQL Trace utility in SQL Server 6.x. You provide criteria and filters to the profiler to create a realtime record of the events taking place on your database. Once the profiler records the events in a trace file, you can analyze them to identify problem areas, or you can replay the trace file to re-create and debug problems. To invoke the profiler from the Enterprise Manager, select Tools | SQL Profiler.

The first step in using the SQL Server Profiler is to define your trace by selecting the database events you want to monitor. The profiler includes several sample traces to help you get started ( see Table 1 ). You can use the standard sample traces as they are, or you can customize them to meet your needs. To create a new trace, select File | New | Trace. After you provide the name and login information of the SQL Server you want to connect to, the Trace Properties dialog box appears. Select the sample trace you plan to use from the Template Name dropdown box; or, if you're using a custom trace, click on the General tab and specify your trace's name.

Next, specify whether you want your trace results saved in a location besides the profiler. To log the trace to an external file, select the General tab, then click on the Capture to File checkbox. At the prompt, enter the name and location of the file you want the trace saved to. Similarly, if you want to save the trace to a table within your database, select the Capture to Table checkbox, and at the prompt, enter the table name you want to create and specify the database to create it in. Saving your trace to an external file is faster and consumes less memory, but storing it in a table provides more flexibility when you examine the trace results. On the General tab, you can also set the trace to shut itself off automatically at a specified time—check the Enable Trace Stop Time checkbox, and fill in the trace's ending date and time.

Choose Your Events
The second tab in the Trace Properties dialog box—the Events tab—allows you to specify the events you want to capture ( see Figure 1 ). Trace events comprise 13 categories: Cursors, Database, Errors and Warnings, Locks, Objects, Performance, Scans, Security Audit, Server, Stored Procedures, Transactions, T-SQL, and User Configurable. The events list might seem daunting at first, but the options seem much simpler once you become familiar with using the profiler. In the sample traces, several events are preselected. You can leave these selections unchanged, or you can add and remove events to customize your sample trace.

	Figure 1 | Specify Trace Settings.

To add an event to your trace, drop down the treeview for that category. Then select which event(s) you want to add and click on the Add button. For example, if you want to create a trace that shows the stored procedures being executed, add the SP:Starting and SP:Completed events from the Stored Procedures category. Both events activate a log to the trace file whenever a stored procedure starts and ends. For information about events within a stored procedure, add the SP:StmtStarting and SP:StmtCompleted events from the same category. These events generate a log to the trace file whenever a statement within a stored procedure begins and ends.

The third tab in the Trace Properties dialog box is the Data Columns tab. This tab allows you to specify events information you want to view. Data column options include viewing the NT and/or SQL Server username, start time, end time, server name, and duration. Not all events support all data columns; the SQL Server Books Online that ships with SQL Server describes which data columns each event supports ( see Resources ). As with the Events tab, several preselected data columns appear for the sample traces. You can modify the existing selections using the Add and Remove buttons.

When you add a data column to your trace, it appears in the Columns section of the Selected Data listbox. To change the order of your selections, use the up and down buttons and move data columns into the Groups section of the Selected Data listbox. Adding data columns to the Groups section indicates that you want to see your trace file results grouped by the specified columns. Grouping the data this way can be an effective aid to analyzing your trace results.

Figure 2 | Set Trace Filters.

The final tab in the Trace Properties dialog box is the Filters tab ( see Figure 2 ). This tab allows you to prevent certain data from appearing in your trace. In most cases, the SQL Server Profiler outputs more data than you need. Setting the proper filters can make your trace easier to read and understand by limiting it only to the necessary data. In addition, minimizing the number of recorded events and setting filters help ensure the trace's execution has a minimal effect on your database's performance.

When you select a data column to be filtered from the Trace Events Criteria listbox, you must provide additional information. The details you provide for a filter vary depending on the specific data column being filtered. Some data columns—such as application name, filename, and owner name—allow you to enter "Like" and "Not Like" text strings. By default, the actions on your database that the profiler causes are filtered out of all traces. You can modify this: Drop down the Trace Event Criteria treeview to ApplicationName, select the "Not like" option, and delete the value for SQL Profiler.

Other data columns, such as Writes, Reads, and Duration, allow you to specify values for equal to, not equal to, greater than or equal to, and less than or equal to. If you're interested in capturing information about slow-running commands in your database, select Duration from the Trace Event Criteria treeview and enter 2,000 in the greater than or equal to field. This filters your trace so only actions of more than 2,000 milliseconds are recorded. Filters accept the "%" character as a wildcard, and you can specify more than one string value by separating the entries with a semicolon.

Play it Again
Whether you create a trace from scratch or use a sample trace, the SQL Server Profiler can replay your trace files, which allows you to re-create scenarios for isolating problems and their causes. A trace file must capture certain events to be eligible for a replay (see the SQL Server Books Online for a description of the events required for replay). But beware: When you replay a trace, it doesn 't just simulate the actions—it actually re-executes all the trace's actions and commands. Replaying the trace affects your data if it includes statements that modify the data, such as Insert or Update statements.

If you plan to work with the SQL Server Profiler, it's worthwhile to become familiar with several of SQL Server's extended stored procedures. These can enhance the functionality available to you through the SQL Server Profiler. The extended stored procedures that deal with traces directly begin with "xp_trace%". Some common applications of extended stored procedures include using them to schedule and automate traces and to send trace information to the Windows log. A complete list and description of the xp_trace% stored procedures is available in the SQL Server Books Online.

Mary V. Hooke is a contributing editor for VBPJ. In addition, she is the president of Distinctive Development, a firm that develops custom Internet and intranet applications in the New York City area. Reach her by e-mail at mhooke@DistinctiveDevelopment.com.