SQL Trace for Query Optimization

DiscountASP recently introduced SQL profiling service. The profiling service produces a trace file. The results of the trace are especially useful in determining the source of real-world problems that you are unable to replicate in your development environment. The goal is query optimization, and this article will give you some guidelines to help get the most out of your SQL database.
The query optimization process can be broken into two main steps:

• Isolating long-running queries.
• Identifying the cause of long-running queries.

The first step in optimizing queries process is to locate the queries that have the longest run-time, and the best tool for that is SQL Trace. Next, you need to analyze the long-running queries to determine where exactly they are spending their time and whether they can be improved.

Replicating the Problem

In order to be able to successfully locate the troublesome queries, the problem should be replicated while SQL Trace is running. This can be the most difficult part of the process because the issue can be random and intermittent, and trace can only be run for a very short period of time. Try to determine when your site is most likely to experience the problem and use that time in your trace request.

Requesting the Trace

When requesting the trace, the following information should be provided:

• The amount of time to capture ranging from 2 to 15 minutes in 1 minute increments.
• The exact date and time that the capture should start running.
• The exact database name that the trace will be running against.

Getting the trace file

When the trace session is complete, the trace file will be placed in your site and you will be notified by email of your request completion. At this time you can do a number of things with the trace file including, but not limited to:

• Opening and analyzing trace results in Profiler. Microsoft SQL Server Profiler is a graphical user interface to SQL Trace. Although you can view and analyze the trace results in Profiler, it is not useful for this task due to the large amount of information presented and because it lacks the capabilities of sorting columns.
• Importing trace results to a table. This is the most powerful way to analyze the trace results since you can run custom queries against this table and thus have the most flexibility. A trace table can be used by Tuning Advisor and Profiler in the same way the trace file is used.
• Using other third-party tools to analyze trace and tune your database.

Importing Trace into a Table

Start Management Studio, open a new query window and use the following T-SQL code to import your trace file to a table:

 USE [MyDatabase]
 GO
 SELECT * INTO MyTraceTable
 FROM ::fn_trace_gettable('c:\MyTraceFile.trc', default)

Replace [MyDatabase]with the database name where you are creating MyTraceTable. Replace:

 'c:\MyTraceFile.trc'

With your actual trace file name and the path to the file.

Locating the Long-running Queries

You are now free to run any custom query against your trace table or you can use the sample add-hoc query below. This query locates queries that run for 1 millisecond or more and sorts them by duration with highest duration being on top. In this query I selected only three columns: EventClass, Duration, and TextData. The TextData column will contain the complete T-SQL code of the captured queries and can be run against your production database directly.

 SELECT
     TE.name           AS [Trace Event],
     T.TextData        AS [T-SQL Code], -- actual T-SQL Code
     (T.Duration/1000) AS [Duration (ms)] -- time in milliseconds
 FROM
     dbo.MyTraceTable T
 JOIN
     sys.trace_events TE
 ON
     T.EventClass = TE.trace_event_id
 WHERE Duration > 1000 -- Filter out the queries that run for less then 1ms
 ORDER BY Duration DESC

Beginning with SQL Server 2005, the server reports the duration of an event is reported in microseconds. That’s why I divided it by 1000 to convert to milliseconds.

Examining Long-running Queries

You can now see the troublesome queries in the query results window after you run the query against your trace table above. Select the T-SQL code to examine by right clicking on T-SQL Code row, then copy and paste it to a new query window. You can now format this code in the query window the way you want and examine it noting which tables and columns are being used.

Replaying the Query

You can run any query in from the trace results against your database again. The good news is that you can replay those queries against your production database directly, whereas you cannot replay the trace using profiler and you cannot run tuning advisor against your production database because that requires sysadmin permissions that cannot be granted on production server.  At this point you may be able to fix the timing issue by indexing the tables and columns being used in the troublesome query. You can also examine the query execution plan to locate the exact problem if you have this advanced knowledge.

Dmitri Gropen
Technical Support