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?