Database Reference
In-Depth Information
The preceding query will show the single costliest query, and it is adequate for the tests you're running in this
chapter. You may also want to run a query like this on a production system; however, it's more likely you'll want to
work from aggregations of data, as in this example:
SELECT ee.SQLText,
SUM(Duration) AS SumDuration,
AVG(Duration) AS AvgDuration,
COUNT(Duration) AS CountDuration
FROM dbo.ExEvents AS ee
GROUP BY ee.SQLText;
Executing this query lets you order things by the fields you're most interested in—say,
CountDuration
to get the
most frequently called procedure or
SumDuration
to get the procedure that runs for the longest cumulative amount
of time. You need a method to remove or replace parameters and parameter values. This is necessary in order to
aggregate based on just the procedure name or just the text of the query without the parameters or parameter values
(since these will be constantly changing).
Another mechanism is to simply query the cache to see the costliest queries through there. It is easier than
setting up Extended Events. Further, you'll probably capture most of the bad queries most of the time. Because of
this, if you're just getting started with query tuning your system for the first time, you may want to skip setting up
trace events to identify the costliest queries. However, I've found that as time goes on and you begin to quantify your
systems behaviors, you're going to want the kind of detailed data that using Extended Events provides.
The objective of analyzing the workload is to identify the costliest query (or costly queries in general); the next
section covers how to do this.
Identifying the Costliest Query
As just explained, you can use SSMS or the query technique to identify costly queries for different criteria. The queries
in the workload can be sorted on the
CPU
,
Reads
, or
Writes
column to identify the costliest query, as discussed in
Chapter 3. You can also use aggregate functions to arrive at the cumulative cost, as well as individual costs. In a
production system, knowing the procedure that is accumulating the longest run times, the most CPU usage, or the
largest number of reads and writes is frequently more useful than simply identifying the query that had the highest
numbers one time.
Since the total number of reads usually outnumbers the total number of writes by at least seven to eight times
for even the heaviest OLTP database, sorting the queries on the
Reads
column usually identifies more bad queries
than sorting on the
Writes
column (but you should always test this on your systems). It's also worth looking at the
queries that simply take the longest to execute. As outlined in Chapter 5, you can capture wait states with Performance
Monitor and view those along with a given query to help identify why a particular query is taking a long time to run.
Each system is different. In general, I approach the most frequently called procedures first; then the longest-running;
and finally, those with the most reads. Of course, performance tuning is an iterative process, so you will need to
reexamine each category on a regular basis.
To analyze the sample workload for the worst-performing queries, you need to know how costly the queries
are in terms of duration or reads. Since these values are known only after the query completes its execution, you are
mainly interested in the completed events. (The rationale behind using completed events for performance analysis is
explained in detail in Chapter 6.)
For presentation purposes, open the trace file in SSMS. Figure
25-1
shows the captured trace output after moving
several columns to the grid.
