2

I'm checking out a query which exhibits strangely inconsistent performance.

Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't.

When it actually used a Table Spool, the query's performance seemed to tank (wait time increased significantly compared to when Table Spool is not used).

See below figure:

Execution plans for exact same query

  • Plan #4681967514 has a Table Spool step
  • Plan #4038619871 does NOT have a Table Spool step

Can you provide a guide, a hint on why the exact same query produced wildly different execution plans like this case?

Note: I've pastebinned the query in Chop: http://chopapp.com/#mpc57cq7

1 Answer 1

3

The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled).

So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or recompilation occurs. With one set of parameter values, the optimizer decides it is worthwhile using a table spool; with other parameters, the cost estimations come out differently, and a spool is not deemed beneficial. Once the plan is cached, it will be reused for other parameter values until another recompilation occurs.

The usual range of solutions are available, please see my article, "Parameter Sniffing, Embedding, and the RECOMPILE options" for details.

1
  • Ahh, thanks for the explanation! So it seems there would be no way to fully optimize this query, except by (perhaps) totally redesigning the data structure... Anyways, thanks for the informational links! I'll read them now.
    – pepoluan
    Jan 27, 2015 at 10:21

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.