SET STATISTICS IO- worktable/workfile

Question

I am executiong query, that produces plan:

Statistics IO:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 128, logical reads 5952, physical reads 576, read-ahead reads 6080, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 9, logical reads 90450, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, Some questions

1. Why Statistics IO show higher reads than Profiler?.

As to KB314648 it is OK if if Profiler reports higher numbers than Statistics IO. But Profiler shows 92283 reads at described query, same execution. Does that mean that Profiler does not count workfile/worktable reads?

2. What is difference between "Worktable" and "Workfile"

I have found that:

• work files could be used to store temporary results for hash joins and hash aggregates.
• work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

Is there some physical difference between them?

3. Why there is "Worktable" in this particular case?

Why do i have worktable at all, if there is 0 logical reads? Its included in statistics IO just because there might be need for it (in case of bad estimates)?

Description found on technet seems vague..

4. What "Physical reads" on Workfile means?

Does that mean that there was not enough memory granted to query, so data had to be written to disk while query executes (yellow warning on Hash Match)? Can i assume that whenever I see worktable/workfile in Statistics IO with physical reads, there has been not enough memory granted for query and some intermediate results of query had to bee written to tempdb disk? And whenever i see only logical reads, RAM is used?

5. One "workfile" means exactly one table that is used for one purpose?

If there is more that one workfile/worktable then i cannot know to what operation it is used?

Answer 1

1. Why Statistics IO show higher reads than Profiler?

No idea, sorry. There are often differences because they are measuring different things as alluded to in the knowledge base article. There is no additional documentation on this that I am aware of. You might be able to infer some things through detailed testing, but there's really no guarantee whatever you find will remain consistent across versions and builds. The intended behaviour is inconsistent enough, before accounting for possible bugs.

2. What is difference between "Worktable" and "Workfile"

Both are internal objects; otherwise it is pretty much what the names suggest: A work table has a table-like structure, a work file is file-like. The detailed structure is not visible, though some broad features can be discerned by inspecting their methods and following their execution paths with a debugger.

3. Why there is "Worktable" in this particular case?

A work table is always needed for a (row mode) hashing operation. It is used internally in distributing the input into hash partitions (an overloaded term, not related to table partitioning) and to keep track of status. I have never seen a hashing work table report anything other than zero in statistics output, but then I've never really looked into it.

4. What "Physical reads" on Workfile means?

Work files are part of the mechanism employed when a hash partition spills. Though not documented, the physical and read-ahead reads occur when the execution engine retrieves spilled hash partitions from the work files.

5. One "workfile" means exactly one table that is used for one purpose?

As I recall, there may be multiple instances. There is no way I know of to relate a particular STATISTICS IO line to a particular object or plan node. This is a long-standing limitation. There may be a change to this in SQL Server 2016, though I have not tested to see if it applies to internal temporary objects.

Ultimately, there's not that much useful to be learned from STATISTICS IO output for work files and work tables beyond that which you can learn by looking at other information in a post-execution plan (or via DMVs, Extended Events etc.). I apologise for the incomplete answer, but it's the best I can offer off the top of my head.