Execution plan operators, twelve of them, read the way the back page reads the stars. Find the one that rules your plan this month. Each sign comes with what it means and the one thing it’s asking you to check.
Open the actual plan. Find the operator with the thickest arrow going into it. That’s your sign.
Everything below is true in the way horoscopes are true: written for everyone, and somehow about you.
Clustered Index Scan, the Ram
You charge at the whole table because nobody told you where to look. Bold, tireless, and reading 1,225,000 rows to return 52.
This month, something small will be asked of you and you’ll answer with everything you have. It won’t be appreciated.
Check: the predicate. If there’s a WHERE clause and you’re still scanning, look at the index, selectivity, estimate and cached parameter. A scan can be the right call even when an index exists.
Index Seek, the Bull
Steady, precise, and unbothered. You go straight to the rows and stop. People forget you’re in the plan at all, which is the highest compliment.
Beware of company. A Seek that feeds a Key Lookup is a Bull walking a Goat, and the Goat sets the pace.
Check: the arrow leaving you. If it’s thicker than the estimate said, you’ve been compiled for the wrong customer.
Key Lookup, the Twins
Two of you, always. One finds the key, one goes back for the rest of the row. You are the operator that ran 200,052 times when the plan expected 51.
This month you’ll be blamed for a decision someone else made at compile time.
Check: the columns the lookup fetches. If they’re the same two every time, they belong in the index as INCLUDE columns, and you’ll vanish.
Nested Loops, the Crab
You take the outer input one row at a time and, for each, walk the inner side. Beautiful when the outer side is small. Sideways when it isn’t.
Your happiness this month depends entirely on someone else’s row count.
Check: the number of executions on the inner operator. That is your true cost. The estimate prices the executions it expected, so a bad estimate is what hides it.
Hash Match, the Lion
You build a table in memory and everyone else has to come through it. Confident, generous with resources, occasionally spilling onto the floor.
A memory grant is coming your way this month. It will be the wrong size. It is always the wrong size the first time.
Check: the warnings on the operator. Open the triangle and read it. A spill warning means a sort or a hash ran out of room and used tempdb.
Sort, the Maiden
Order from chaos, at a price. You ask for memory before you start and you don’t know how many rows are coming.
The month brings a grant sized for 50 rows and a workload of 200,052. Something will be written to disk that was never meant to touch it.
Check: whether an index could hand rows over already sorted. When the ORDER BY column leads the index key, you can disappear from the plan.
Merge Join, the Scales
Balanced, fair, and demanding. Both inputs must arrive sorted or you refuse to work. When they do, nobody’s faster.
People will add a Sort in front of you this month and call it your fault.
Check: where the sort order came from. If an index supplied it, you’re the right choice. If a Sort operator supplied it, someone should look at the indexes.
Table Spool, the Scorpion
You save rows to tempdb so they can be played back. Quiet, hidden, and holding on to more than anyone realises.
A query will run “fine” this month while you write the same rows to disk four hundred times.
Check: rebinds and rewinds on your properties. High rebinds mean the spool is being rebuilt, and the saving you were supposed to provide isn’t happening.
Stream Aggregate, the Archer
You count, sum and group in one pass, as long as the rows arrive in order. Efficient, direct, and dependent on somebody upstream.
This month you will be replaced by a Hash Aggregate because the order you needed wasn’t there. Take it up with the index.
Check: the GROUP BY columns against the index key. If an index already hands the rows over grouped, you stay. If nothing supplies that order, the Lion takes your place.
Parallelism, the Goat
You split the work across schedulers and gather it back, climbing the mountain in eight places at once. Impressive until one thread finds the steep bit.
Watch for a month of CXPACKET waits. That’s the rest of the team waiting at the summit for the slow climber.
Check: row distribution across threads in the actual plan. If one thread did all the rows, the parallelism cost you and gained you nothing.
Compute Scalar, the Water Bearer
You carry expressions from one operator to the next. Cheap, everywhere, and dismissed as nothing.
Except when you’re a scalar function in disguise. Then you run once per row and the plan hides your cost in a single innocent box.
Check: whether any expression inside you calls a user-defined function. If it does, the estimated cost of this operator is fiction.
Filter, the Fish
You let some rows through and hold the rest back, late in the plan, after the expensive work is done.
The month brings the feeling of having done everything right and still being slow. That’s because the filtering happened after the join, not before.
Check: whether the predicate could have been applied earlier. A Filter operator sitting above a scan usually means the scan read rows it was about to throw away.
RID Lookup, the Thirteenth Sign
The sign that only appears for heaps. You fetch rows by physical address, and when the row has moved you follow the forwarding pointer.
Your table has no clustered index. That isn’t fate. It’s a decision someone made and nobody revisited.
Check: forwarded record count in sys.dm_db_index_physical_stats. If it’s high, the heap has been quietly doubling your reads.
How to read your execution plan operators
None of this replaces reading the plan properly. It’s a way to make yourself look at the operator with the thick arrow, which is the one that matters.
Every sign above has one check. Do the check. The stars can wait.
If your plans have been in the wrong sign for a while, I read them for a living.
A plan is not a prophecy, it is a receipt.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
