I recently worked with a customer where they had a query that despite taking a consistent execution plan had very different runtimes (as identified by the plan hash).  This was not a case of parameter sniffing – instead this is what I refer to as plan shortcuts.

The query I’m using for this example is below.  It is looking for the existence of a customer record across two tables.  Of course, there are some issues with denormalization and implicit conversions, but for example’s sake I did some bad things:

IF EXISTS
(
SELECT 1 FROM SalesLT.Customer WHERE CustomerID = @CustomerID
UNION
SELECT 1 FROM CustomerAccount WHERE AccountNbr = 'ACT-' + CONVERT(NVARCHAR, @CustomerID)
)
SET @CustomerExists = 1
ELSE
SET @CustomerExists = 0
SELECT @CustomerExists AS CustomerExists

The important points to note in this example are this is an EXISTS query.  It checks two different tables via a UNION.

The top part of the UNION is efficient and performs an index seek.

The bottom part of the UNION is inefficient and does a large table scan (as a result of an implicit type conversion).

Since this query is in an EXISTS, if the top part of the UNION returns any rows it does not need to perform the bottom part of the UNION which is inefficient.  As a result, the query can take a shortcut and not execute the bottom part of the UNION.

Here’s an example showing this with different values for the @CustomerID.  Below is the STATISTICS TIME and STATISTICS IO on.  In this run the CustomerID existed in the SalesLT.Customer table so the bottom half of the UNION did not need to execute.  The shortcut caused this to execute in 0 milliseconds.

Below is the STATISTICS TIME and STATISTICS IO output where the @CustomerID did not exist in SalesLT.Customer.  As a result, it had to execute the bottom part of the UNION.  Not being able to take a shortcut caused this to execute in 26,506 milliseconds.

Final thoughts

This query is just one example of where a plan taking a shortcut might cause inconsistent performance.  There are many cases besides this EXISTS with a UNION that a plan can have large variances in runtimes despite using the same plan.  Happy tuning!