T-SQL Clustered Index Seek Performance

Question

Usual blather... query takes too long to run... blah blah. Long question. blah. Obviously, I am looking at different ways of rewriting the query; but that is not what this post is about.

To resolve a "spill to tempdb" warning in a query, I have already

1. rebuilt all of the indexes in the database
2. updated all of the statistics on the tables and indexes

This fixed the "spill to tempdb" warning and improved the query performance.

Since rebuilding indexes and statistics resulted in a huge performance gain for one query (with out having to rewrite it), this got me thinking about how to improve the performance of other queries without rewriting them.

I have a nice big query that joins about 20 tables, does lots of fancy stuff I am not posting here, but takes about 6900ms to run.

Looking at the actual execution plan, I see 4 steps that have a total cost of 79%; so "a-hah" that is where the performance problem is. 3 steps are "clustered index seek" on PK_Job and the 4th step is an "Index lazy spool".

execution plan slow query

So, I break out those elements into a standalone query to investigate further... I get the "same" 4 steps in the execution plan, with a cost of 97%, only the query time is blazing fast 34ms. ... WTF? where did the performance problem disappear to?

execution plan fast query

I expected the additional tables to increase the query time; but I am not expecting the execution time to query this one Job table to go from 30ms to 4500ms.

-- this takes 34ms
select * 
from equip e 
left join job jf on (jf.jobid = e.jobidf) 
left join job jd on (jd.jobid = e.jobidd)
left join job jr on (jr.jobid = e.jobidd)


-- this takes 6900ms
select * 
from equip e 
left join job jf on (jf.jobid = e.jobidf) 
left join job jd on (jd.jobid = e.jobidd)
left join job jr on (jr.jobid = e.jobidd)
-- add another 20 tables in here..

Question 1: what should I look at in the two execution plans to identify why the execution time (of the clustered index seek) on this table goes from 30ms to 4500ms?

So, thinking this might have something to do with the statistics I review the index statistics on the PK_Job = JobID (which is an Int column) the histogram ranges look useless... all the "current" records are lumped together in one range (row 21 in the image). Standard problem with a PK that increments, new data is always in the last range; that is 99.999% of the JobID values that are referenced are in the one histogram range. I tried adding a filtered statistic, but that had no impact on the actual execution plan.

output from DBCC SHOW_STAT for PK_Job

Question 2: are the above PK_Job statistics a contributing factor to the complicated query being slow? That is, would "fixing" the statistics help with the complicated query? if so, what could that fix look like?

Again: I know, rewrite the query. Post more of the code (all 1500 lines of it that no one will find of any use). blah, blah.

What I would like are tips on what to look at in order to answer Q1 and Q2. Thanks in advance!

Question 3: why would a simple IIF add 100ms to a query? the "compute scalar" nodes all show a cost of 0%, but the IIF doubles the execution time of the query.

adding this to select doubles execution time from 90ms to 180ms; Case statements are just as bad too.

IFF(X.Okay = 1, '', 'N') As OkayDesc

Next observation: Actual execution plan shows query cost relative to batch of 98%; but STATISTICS TIME shows cpu time of 141 ms; however batch cpu time is 3640 ms.

Question 4: why doesn't the query cost % (relative to batch) match up with statement cpu time?

Answer 1

The SQL Engine is pretty smart in optimizing badly written queries in most of the cases. But, when a query is too complex, sometimes it cannot use these optimizations and even perform bad.

So, you are asking:

I break out those elements into a standalone query to investigate further... I get the "same" 4 steps in the execution plan, with a cost of 97%, only the query time is blazing fast 34ms? where did the performance problem disappear to?

The answer is pretty simple. Breaking the queries and materializing the data in @table or #table helps the engine to understand better with what amount of that it is working and built a better plan.

Brent Ozar wrote about this yesterday giving an example how bad a big query can be.

If you want more details about how to optimize your query via rewriting, you need to provide more details, but in my practice, in most of the cases simplifying the query and materializing the data in #temp tables (as we can use parallel operations using them) is giving good results.