Reads in statement tree differ from Table I/O Reads

turnoffafterlife 2016-03-08 20:36:32

The reads in the 'Results' reports 20 for statement 11 but the 'Table I/O' reports 7. I'm new to the product and curious why there is a difference.

Thanks.

Aaron Bertrand 2016-03-10 01:22:48
The Table I/O tab is showing the logical reads as reported by SET STATISTICS IO ON.

The Reads column in the statement tree is showing the total, aggregated logical reads for the entire query execution (akin to the read metric you'll find in Profiler/Server-side trace or Extended Events).

When you have access to multiple tables, and then perform joins, the same pages can be read in memory multiple times as those joins are processed. This can sometimes lead to a higher number in the latter metric, because it counds those multiple page reads, while the SET STATISTICS IO metrics only count the base table access. There are other potential reasons described in KB #314648 but, long story short, the Reads column up top is not necessarily just the reads directly associated with your query.

I hope that makes sense, but you should be able to confirm what I'm saying by running a trace and then executing that same query with SET STATISTICS IO ON. You should see 2/2/3 in the message pane for the stats output, and the trace should yield the higher number. If you look at statement 12 you'll find the same thing, it's just tougher to visualize – the Table I/O tab shows 310 reads while the statement tree shows 345. Statement 8 shows 104 reads above and only 1 read in the Table I/O tab.

You should not expect Table I/O numbers to add up to the number shown in the Reads column (though it can happen that way depending on the plan shape and operators involved). You can also see cases where the Reads figure is lower than the sum of STATISTICS I/O.

See this answer on dba.stackexchange for some other thoughts.