How to Find the Worst-Performing Queries in a Database Server

A common complaint among database users is the sporadic occurrence of errors like this:

Timeout expired. The timeout period elapsed
prior to completion of the operation
or the server is not responding.
Exception type: SqlException
Source: .Net SqlClient Data Provider

The errors may come from a variety of procedures or queries. While occasionally the query itself may need to be optimized, the problem may stem from another unrelated operation that is bogging the system down. At that point the problem becomes a general tuning issue that may involve queries that have nothing to do with the “offending” query.

There is a simple way to gain insight into which query or queries deserve looking at. Using SQL Profiler, you can develop a list of queries, sorted by average reads descending. This result set will become your “hit list” of query optimizations that will yield the greatest return on time invested.

WHY QUERIES DO EXCESSIVE READS

(1) There is no index available to support a seek-based lookup. This may occur when a query has no WHERE CLAUSE. Your best defense here is to avoid this situation altogether. If you must query without a WHERE CLAUSE, keep the result set very narrow and select it as infrequently as possible.

(2) There is no index that supports the WHERE CLAUSE. A WHERE CLAUSE across joined data (i.e. multiple filter expressions on different source tables) may be particularly problematic, as it is usually not possible for the query optimizer to figure out which index to use on the clause. You may improve performance in this case by joining two subqueries, each on its own table with its own WHERE CLAUSE.

(3) The WHERE CLAUSE is applied to a view. Avoid using views in queries that will be subsequently filtered. Selecting even a single row, based on its primary key, from a view will cause a scan of the underlying table.

Note that INSERT and UPDATE queries are not immune to these problems, as many of these use filters and embedded joins to do their work.

HOW TO USE SQL PROFILER TO FIND THE WORST OFFENDERS

All queries in a database can be optimized, and optimizing most queries is a waste of time and effort. This simple procedure will enable you to identify the worst offenders in your server and target your efforts.

(1) Start SQL Profiler. Open a new trace on the server that you wish to observe.

(2) In the Trace Properties screen (Events Selection tab), check “Show All Events.” Find the “Stored Procedures” events and choose the “SP:StmtCompleted” event. This will enable queries within a stored procedure to be separately reported.

(3) The default set of Column Filters will work well, so no changes are needed there. You can, however, keep the trace volume down by filtering reads for value >= 100. Queries with reads < 100 are not of interest to us in this context.

(4) In the Trace Properties screen (General tab), check “Save To Table.” Connect to the database where the trace will be saved (probably on your own computer so that you can keep it as evidence), specify a database, an owner, and a table. The database and owner should exist. The table should NOT exist. Press RUN to activate the trace. This will cause the table to be created (or overwritten if it already exists).

(5) As the trace runs, put the server through its paces. The idea is to create the environment where the problem manifests itself. You can concurrently use Management Studio to query the status of the trace table. Assuming you called the table Trace1, this query will get you started.

SELECT TOP 10 CAST(TextData AS Varchar(255))AS Query
,Count(*) AS TimesCalled
, AVG(READS) AS MeanReads
FROM [dbo].[Trace1] WITH (NOLOCK)
GROUP BY CAST(TextData AS Varchar(255))
ORDER BY MEANREADS DESC

The result set will contain three columns. The Query column will produce recognizable SQL statements from within stored procedures. The TimesCalled column will reveal the prevalence of the call. And the MeanReads column will reveal the average reads done by the query each time it is called. This result set will produce powerful evidence of where the problems are in a database server, enabling you to clean up your own mess(es) before you escalate the issue within the department.

(6) As you repeat the test, use a different table name each time. This will allow you to chart progress as you fix stored procedures.