Tuning Your Database with SQL Trace

If you believe Microsoft's claims, Microsoft SQL Server is the fastest relational database management system for Windows NT. But you can't assume that your applications will run fast just because SQL Server cranks out high numbers on the Transaction Processing Council's C benchmark (TPC-C). Here's some common-sense advice for getting your SQL Server to run faster.

Many factors--hardware, the network, SQL Server configuration options, and application design--affect the performance of a database system. Most knowledgeable database professionals realize that their data-access application is key; you must tune how the application interacts with SQL Server to achieve the best performance. Fortunately, Microsoft simplifies database-level application profiling with SQL Trace. I'll explain how to run SQL Trace and give you pointers for analyzing its output. In future articles, I'll address common application problems. The fixes are surprisingly simple, and can offer huge performance improvements.

What Is SQL Trace?
SQL Trace is SQL Server's built-in utility that monitors and records SQL Server 6.5 database activity. This utility can display server activity; create filters that focus on the actions of particular users, applications, or workstations; and filter at the SQL command level. SQL Trace operates by capturing SQL statements or remote procedure calls (RPCs) sent to any SQL Server 6.5 system. You can save trace activity to disk in an ISQL/w-compatible script that you can replay against a SQL Server, or you can set up an activity log file that captures the SQL command and a variety of performance information.

SQL Trace is the most powerful tool in my tuning bag of tricks. It lets me see the big picture of application-level interaction with SQL Server without knowing anything about the application or looking at the source code. Modern data access mechanisms such as Open Database Connectivity (ODBC) and Object Linking and Embedding Database (OLE DB) and their high-level distributed component object model (DCOM) abstractions such as Remote Data Objects (RDO), ActiveX Data Object (ADO), and Data Access Object (DAO) are great programming tools. But these tools hide what's going on between the client and the server. SQL Trace can show programmers what their application is doing.

Figure 1 demonstrates how ODBC applications often do surprising things. This simple SQL Trace output shows all the Transact-SQL (T-SQL) commands that Microsoft Access issued when I opened an attached link to the pubs..authors table in Datasheet View. You might be thinking, "Hey! I thought we were doing only a SELECT * FROM authors. Where did all these statements come from?" Here's what happens.

First, Access runs a SELECT statement that returns all the values for the au_id primary key column. Then, it creates a stored procedure that accepts 10 parameters and runs a SELECT statement similar to Listing 1. Because the authors table has 24 rows, Access runs its new procedure three times to return all 24 rows. The first two calls ask for 10 rows each, and the last call asks for the final 4 rows.

This example demonstrates an important point: Applications (e.g., ODBC) using data-access middleware rarely do exactly what you think they are doing. The final outcome the user sees might be the same as the result obtained without using middleware, but ODBC doesn't always get the data efficiently. To be fair, the inefficiency is not ODBC's fault. ODBC can be efficient and flexible, and it usually provides several ways to accomplish the same result so that developers can tune their system. This flexibility is a blessing and a curse. It's great if you know what your options are but miserable if the applications developer allows defaults that aren't best for your needs. SQL Trace lets you see ODBC's choices and whether its choices were appropriate for your environment.

Running SQL Trace
You can run SQL Trace as a GUI utility or through the xp_sqltrace interface. The GUI version--a visual wrapper around xp_sqltrace--is easy to use and helpful for ad hoc troubleshooting. You start SQL Trace from Programs, SQL Server, SQL Trace. Screens 1 and 2 show SQL Trace's GUI.

In Screen 1, I'm editing the filter properties of the trace Show Me The SQL!!!. I've selected options to display the trace onscreen, using a separate window for each connection. I've also selected the options to log the trace to an .sql file for replay and a .log file for future analysis. I'm capturing connections from all users, workstations, and applications, but I could filter on those attributes. The Filter dialog box on the Events tab that Screen 2 shows lets me filter at the statement level. I want to see only T-SQL statements that reference the TopSecretTable object.

The GUI version is handy, but I've found the extended stored procedure version more useful when I'm doing a full application profile. Xp_sqltrace offers more control over options, and the audit mode lets me run a long trace without being logged into a machine overnight. I can also stop and start a trace from a timed SQLExec task. Xp_sqltrace is well documented in Books Online (BOL), and I strongly encourage you to read the information. (However, BOL isn't perfect. The sidebar "Bugs in BOL's SQL Trace Table," page 135, describes two errors that you need to be aware of.)

One important xp_sqltrace command-line option is @FullText, which controls whether the trace captures the entire T-SQL batch being executed or defaults to a value of 0, which grabs only the first 255 characters. My experience has shown that you want to set this option to 1, which captures the entire batch. Nothing is more frustrating than trying to tune or troubleshoot a query that's chopped in half.

Running xp_sqltrace is straightforward, but repeatedly typing all those option settings is tedious. I like to create stored procedure wrappers around xp_sqltrace and set the options to my favorite defaults. That way, I don't have to type as much to stop and start a trace. Listing 2 shows two stored procedures you can use to get started.

What effect does running SQL Trace have on security and performance? Loading the SQL Server client utilities installs SQL Trace; therefore, many people have access to the tool. But you can easily control who can use it by setting appropriate permission levels on xp_sqltrace within the master database; by default, only the sa permission can run xp_sqltrace.

Monitoring database performance can degrade the performance you're trying to monitor. The trick is keeping overhead low to avoid significantly skewing or misrepresenting results. Fortunately, the performance impact of running xp_sqltrace is negligible. I haven't run a lot of simultaneous active traces or complex filters, but I've found that when I run one active trace in audit mode, logging results to a local text file on the server imposes almost no performance penalty. I don't like to log results directly to a local SQL table because this action can easily skew results in some tuning problems. But writing to a text file seems to have little effect if the server isn't completely I/O bound. If the server is I/O bound, write the log file to a remote network share and hope that the network isn't bottlenecked, too.

Loading SQL Trace Output for Analysis
SQL Trace output is almost useless in its raw form because it includes an overwhelming amount of information. To make my life easier, I load trace files back into a SQL Server table and run queries against it to help me understand what activity the server was performing. I can run a whole range of queries that help me quickly pinpoint inefficient parts of the application. SQL Trace's value is directly related to the cleverness of the queries you write to analyze the data. I'll show you one powerful analysis query here and describe other queries in future articles.

Table 1 lists the information that a SQL Trace log file captures. The log file is a standard ASCII file with tab-delimited columns.

I recommend using bulk copy program (bcp) to load your data into SQL Server for analysis. Bcp isn't the most user-friendly utility, but bcp'ing trace data is easy because the log file is tab delimited and you load the log file into a SQL Server table that exactly matches the file layout. My table looks like Listing 3. If you're new to bcp, the following command will get you started:

bcp pubs..SQL Trace in c:\activity.log 
-Usa -Psecret -S -c

   Previous  [1]  2  Next