Reason for Mystery Worktable
The bad news is that the reason for the work table is not directly visible in the query plan. The good news is there are only a small number of likely reasons for it.
The plan with a worktable has a non-parallel plan reason of "MaxDOPSetToOne". When this query was compiled, either the server level DOP was set to one, or the processor affinity was set such that only one processor was available to SQL Server at that time.
The second plan has an estimated available degree of parallelism of four (suggesting SQL Server had eight logical processors available at the time). Both plans are non-parallel of course, but this difference in estimated DOP might affect internal aspects of the execution plan that are not visible in show plan output.
Something you could check: if the work table only appears when available DOP is one, you have your reason, if not a full explanation.
Both execution plans use an optimization on the Nested Loops joins, to prefetch data from the inner side that will be needed by the join soon. This is a similar mechanism to the normal read-ahead used when scanning, or range-scanning, a base table or index. Prefetching is enabled (some ordered, some unordered) on all the Nested Loops joins in both plans.
The execution plan that does not use a worktable performs very little actual read-ahead:
Whereas the worktable query has read-ahead I/O on all the non-temporary tables:
The most likely cause of that difference is that the data is usually in memory (so read-ahead/prefetch does no work) but when the work-table query executed, the data was not already in memory (for whatever reason) so read-ahead/nest-loop-prefetch found real work to do. In this scenario, the worktable is used internally when LOB prefetching is actually performed. You could test you query with a cold buffer pool to validate this. (It is not immediately obvious from the plans which tables have LOB columns needed by this query, but that information should be readily available to you.)
You generally would want to leave read-ahead and prefetching enabled, though you do have the option to disable both kinds if that becomes necessary. This is very much a last resort, so we should try all other options first. There is a documented trace flag (652) to disable base-table read-ahead, and 8744 for Nested Loops range-prefetching.
I have to mention this, though I do not believe it is the direct answer to your question – spilling sorts do not use work tables (as I believe someone suggested to you).
The input to the sort has a poor cardinality estimate caused by the Filter turning out to be rather less selective than the optimizer anticipated. The data set at that point is not very large, so I would materialize it (the plan from the Filter downward) into a heap temporary table.
This would give the optimizer exact cardinality and distribution information on that set for the Sort (require for the ROW_NUMBER later on). Making this change might affect the work table "issue" as a side-effect of course.
- Test materializing the input to the sort
- Check if the worktable only appears when available DOP is 1
- Test the query with a cold buffer pool
- Consider disabling read-ahead for this query