Same query sometimes uses a Table Spool 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:




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
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.