question

Martin 1 avatar image
Martin 1 asked

Worktable storage format

As far as I'm aware the format of the various work tables used by SQL Server is not officially documented. Is anyone aware if there is any unofficial documentation on them anywhere?
sql-serverperformanceperformance-tuningtempdb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Martin, worktables are internal structures that you cannot create, destroy or read. As per BOL >The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed. If you are asking what format, as in datatypes etc, then I would be led to believe that the structure will be defined by the datatypes of the data that is being dealt with.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No that isn't what I'm asking really. I'll add a specific example to the question which demonstrates why I am interested.
0 Likes 0 ·
Ah actually I can't come up with a specific example where this matters. Reason for the question was that I was comparing some execution plans and it seemed like one that had an eager spool added was doing less reads than one that read from the base table directly even though the spool had all columns from the base table. So I hypothesised it might use a more compact format but I can't reproduce now so think there must have been something else going on.
0 Likes 0 ·
Regarding my comment above it turned out that the source DB had space reserved for `VERSIONING_INFO` in the pages. This was eliminated when copying into the spool hence reduced number of reads. Still I'll leave this question open as I'd be interested if anyone has looked into this specifically anyway.
0 Likes 0 ·
Instead of looking at work tables I would look at removing the eager spool, possibly by creating an index. They are performance killers
0 Likes 0 ·
@Scot - That doesn't help answer my question. I just like to know these things so I get a better mental understanding of what's happening.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I don't know the answer to your question. But I'd say if it's not explained in SQL Server Internals, by Kalen Delaney, you're probably out of luck. Have you read that book?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes I've read that book. And that explains in great detail regular table storage format I was hoping someone, somewhere might have already done some work with `DBCC PAGE` to look at these work tables.
0 Likes 0 ·
I've just had a look at my copy - no sign of Worktables in the index!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.