Breaking Down Complex Execution Plans

As a DBA, whether working as a full time employee of a company or as a consultant, you’re often handed stored procedures or queries long after they’ve been “finished.” These finished queries are frequently enormous, free-flowing affairs with between 50 and 500 statements joining up to 80 tables, views, derived tables and functions. Together they arrive at some set of results. We’re brought in because the finished query runs slowly or is causing deadlocks or blocks or takes forever to recompile, whatever is wrong with it. Now you have to make sense out of this incredibly complex query. How do you break it down?

The short answer; just like eating an elephant, one bite at a time. The long answer...There are two levels to the complexity that you have to address. The first level of complexity is the shear number of statements that have to be dealt with. Which ones are causing the majority of the load? Which ones can I safely ignore. The second level of complexity is an execution plan that looks something like this:


Figure 1

Which operations are causing the bottleneck? Where do I spend my time?

This article will try to provide you with a few tools so that you can more readily answer these questions for yourself. I’m going to introduce a number of options and tricks using SQL Server Management Studio, the query window, execution plans and the SQL Server Profier. However, this isn’t meant to be a tutorial on any of these subjects. If you’re just getting started, get familiar with those topics first then come back and tackle that complex query plan using these hints and tips.

Statements, Statements Everywhere
The incredibly extreme number of statements that some of the people writing stored procedures are able to cram in boggles the mind. Three different approaches deal with this glut of statements with varying ease of use and accuracy of measure. First, using the graphical execution plan’s “Query cost (relative to the batch)” is easy and immediately available as a part of displaying the execution plan. It also tends to be somewhat inaccurate. So inaccurate that many DBA’s won’t use it at all while others use it merely as an easy starting point. Next, the Advanced set of Query Options allow you to capture statement time and I/O. These are very accurate, but sometimes difficult to interpret accurately. Finally, Profiler provides a mechanism for capturing individual statements within a query or procedure. While this isn’t as convenient to the execution plan as the “Query Cost,” it is as easy to read. It’s just as accurate as capturing time and I/O but much easier to interpret.

“Query Cost”
Most of the time, the values displayed in “Query cost (relative to the batch):” accurately display the relative cost of each statement within the greater context of the batch. The batch can either be a query or a stored procedure. Simply capturing an Execution Plan, Estimated or Actual, through the GUI in Management Studio will automatically list each statement as a separate “query.” They will be numbered in the order in which they were called and an estimate of their cost relative to the batch will be displayed:


Figure 2

This can make it very quick & simple to determine which statement costs the most. The problem is, this cost isn’t based on the actual I/O or time that the statement took to run. Instead it’s based on the optimizer’s Estimated Cost for the statement. These estimates can be off for a number of reasons, including, but not limited to, out of date statistics, missing statistics, table variables, recursive common table expressions, and multi-statement table valued functions, just to name a few. Because these things can skew the Estimated Cost of the statement, they can mask it’s actual cost within the execution of the greater batch.

Here’s an example that’s just about as extreme a difference as possible. Jeff Moden created this example and sent it to me for the express purpose of alerting people to the dangers of using the Query Cost in graphical execution plans. It requires a tally table, also known as a table of numbers. This is simply a table with one column and a list of numbers 1 to n. Included is a simple script to set up a tally table for your test:

CREATE TABLE Tally (Num INT NOT NULL)

DECLARE @i INT -–NOTE: This is a simple, not efficient, load
SET @i = 1
WHILE @i<10000
BEGIN
 INSERT INTO [Tally] (
  [Num]
 ) VALUES (
  /* Num - INT */ @i )
 SET @i = @i + 1
END
GO

Run the following query with “Include Actual Execution Plan” turned on:

SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by (Name with-held)
--=======================================================================================
  PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
DECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.

--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
    SET @DateVal = '2008-01-01'

;with mycte as
     (
       select @DateVal AS DateVal
       union all
       select DateVal + 1
         from    mycte   
         where   DateVal + 1 < DATEADD(yy, 5, @DateVal)
     )
select @BitBucket = d.dateval
from mycte d
OPTION (MAXRECURSION 0)

--===== Turn off the performance counters and print a separator =========================
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
  PRINT REPLICATE('=',90)
GO

--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
  PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
DECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.

--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
    SET @StartDate = '2008-01-01'

SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,5,@StartDate)))
        @BitBucket = @StartDate-1+t.Num
   FROM Tally t
  ORDER BY Num

--===== Turn off the performance counters and print a separator =========================
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
  PRINT REPLICATE('=',90)
GO

You should see something like this as a result:



Figure 3

As you can see, the “Query Cost (relative to the batch):” for the first query is showing up as 0% while the second query is 100% of the cost. But, if we look at the Messages window:



Figure 4