1

I have a query:

select * from Aview where field=20
order by id desc

This returns 2700 rows from the view in about 1 second.

Adding 'top 20' to the query makes MSSQL return in 43 seconds!!

This has been a HARD to reproduce issue, and doing a rebuild of statistics fixes the issue for a couple of days, but then it come back.

I've been working with SQL for decades and I've never once seen a situation where adding a 'top' causes the time to increase.

Looking at the execution plan, it seems to be doing a lazy spool of 960 MILLION rows if you do the top 20, but not if you don't.

0

1 Answer 1

5

The question is light on detail, but there is enough there to attempt an answer.

I've been working with SQL for decades and I've never once seen a situation where adding a 'top' causes the time to increase.

This is more common than one might think. Using TOP introduces a 'row goal' so the optimizer tries to find a plan optimised for a few rows rather than the complete potential result set.

This is a fine and useful optimisation when estimates and other modelling assumptions are correct, but it can go very wrong otherwise. The query hint OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')) exists for these types of situations.

See the Q & A How (and why) does TOP impact an execution plan? for more details.

Looking at the execution plan, it seems to be doing a lazy spool of 960 MILLION rows if you do the top 20, but not if you don't.

This is likely to be a 'performance spool', which optimises for repeated inner-side sub-results. See my article Nested Loops Joins and Performance Spools for more details. Note this type of spool is different from the Eager Spool often used for Halloween Protection.

Again, performance spools are intended to confer a benefit. When they do not, it is possible to eliminate performance spools with the OPTION (NO_PERFORMANCE_SPOOL) query hint. Like all hints, these are advanced tuning options that should only be employed after expert analysis.

You noted:

Changing the query to 'order by CreatedDate desc' changed the table spool from 960M rows to 10K and resolved the issue, (but resulted in the same data) and for now at least, is the 'fix'

This might be a workaround in your particular case, but in an ideal world you would identify the root cause. Query execution and tuning issues can be a complicated affair I'm afraid.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.