Reason for Mystery Worktable

There are 2 identical queries in this question. Why is the first query in step #12 not using a work table but the other one does?

[1]: /storage/temp/308-303-samequery_with_worktableonstep.queryanalysis

avatar image By GokhanVarol 380 asked Feb 27, 2013 at 04:57 PM
more ▼
(comments are locked)
avatar image GokhanVarol Feb 27, 2013 at 08:33 PM

I am not able to post any more images or files here (I contacted the admin for it). The step #12 which has exact duration of 1,047,581 has a work table the identical query plan run at some other time does not have the work table, from what I see everything dop, estimated numbers, actual numbers are the same.

10|10000 characters needed characters left

1 answer: sort voted first

Hi Gokhan,

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.

Available DOP

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.

Prefetching

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:

No worktable I/O

Whereas the worktable query has read-ahead I/O on all the non-temporary tables:

Worktable I/O

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.

Sort spilling

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.

Summary

  1. Test materializing the input to the sort

  2. Check if the worktable only appears when available DOP is 1

  3. Test the query with a cold buffer pool

  4. Consider disabling read-ahead for this query

sp.png (34.9 kB)
sp.png (31.3 kB)
avatar image By SQLkiwi ♦ 6.6k answered Feb 27, 2013 at 09:47 PM
more ▼
(comments are locked)
avatar image GokhanVarol Feb 27, 2013 at 10:17 PM

Yep, it's totally related to cold cache vs hot cache. I thought the work table was adding to the cold cache problem but I realized it's caused by it. Thank you Paul

avatar image GokhanVarol Feb 27, 2013 at 11:09 PM

Thank you Paul I cannot believe what's going on, I ran the same workload under the trace you recommended DBCC TRACEON (652) The query is running almost as fast as it's running on a hot cache (the database is on ssd) Do you think I should disable the prefetch for this workload?

avatar image SQLkiwi ♦ Feb 27, 2013 at 11:43 PM

That trace flag is documented, so if your testing reveals it has a beneficial effect for this particular query, then yes. Use DBCC TRACEON and DBCC TRACEOFF to enable the flag just for this query; 652 does not work with `OPTION QUERYTRACEON`.

Read-ahead does have benefits even on SSD, in general, but there is no arguing with test results in a particular scenario. Please no-one reading this take this as general advice to enable 652 whenever you see a worktable - this is a very specific scenario.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x568
x166
x3
x2

asked: Feb 27, 2013 at 04:57 PM

Seen: 3791 times

Last Updated: Aug 27, 2015 at 01:28 PM