Plan IO vs Reads in profiler

Ranga 2014-05-20 14:15:55

Just trying to understand why sql profiler says 17k reads whereas the SQL plan "Actual Data Size" for each operator (when you hover the mouse over the operator) is so small like 25 B etc totalling not more than 100 B. So, my question is why the plan operators like index seek, Nested Loops etc does not match to the Reads shown in the profiler ? statement1 is the most expensive one with 17k reads as seen in profiler.

Aaron Bertrand 2014-05-20 14:18:02
Profiler says 17,000 read operations or 17,000 bytes?
Ranga 2014-05-20 14:38:34
17268 under reads column in profiler, so i think it is 17268 pages of logical reads.
Aaron Bertrand 2014-05-20 14:45:03
Well the data that moves between operators is the data that is actually moved, not the size of the pages that had to be read in order to select the data before the data is moved. If you look at the index seek on statement1, it only returns one row, but look at the I/O cost (3.866) compared to the clustered index seek on the same statement – it also only returned one row, but the I/O cost incurred to retrieve that one row is much lower (0.003) – which tells me that it had to read far fewer pages to get to that one row. Don't get too caught up confusing data size in the movement between individual operators with reads performed by the entire statement, and pay attention to the Table I/O tab, which tells you how many pages were accessed in the underlying objects (I can't see any info there, but I think it is because the plan you submitted was anonymized).
Ranga 2014-05-20 14:58:31
thanks Aaron….I don't see the Table I/O tab, may be it is not PRO version, but i can see the object that caused the high reads in the statistics I/O in sql query window in my PC. Interesting how it takes 17k reads to get one row with clustered index seek!
Aaron Bertrand 2014-05-20 15:02:04
The big cost is from the index seek, not the clustered index seek, and it actually looks like a range scan disguised as a seek (hint: predicate is <=, not =) that only happened to produce one row (but again, it may have had to skip a lot of rows to find it). Tough to know exactly what is going on without the actual statement and understanding the query logic. As an aside, Table I/O has nothing to do with the PRO version - do you see it if you generate a new actual plan, before anonymizing it?
SQLkiwi 2014-05-22 07:25:29
>So, my question is why the plan operators like index seek, Nested Loops etc does not match to the Reads shown in the profiler?

The row size numbers are estimates of the size of the data that actually moves between plan operators. Plan Explorer enhances this information by adding a calculated 'actual data size' value based on the actual number of rows multiplied by the estimated row size. These numbers can be useful for giving you feel for the size of data moving around.

The Profiler reads value reflects the number of pages read on behalf of the query to find the data that moves around the plan. In your case, the 17,268 reads are performed by the index seek on Table2.Index2. This is an inequality seek with a residual predicate:

Index Seek

This operation reads so many pages because it seeks to the position of the first record that meets the inequality Column3 <= ScalarString4, then tests the residual predicate ScalarString5. This row does not qualify, so the seek continues testing rows that meet the inequality and residual in key order until it finally finds a match for both conditions. The operator looks as if it seeks to produce a single row, but in reality it is hiding quite a lot of work. Note: The Table I/O tab is not a PRO-only feature, it is populated when you execute the query from PE directly (Get Actual Plan). The information is not collected in a regular query plan, so PE has no way to display it if you import a plan from SSMS.

Ranga 2014-05-22 17:57:44
Thanks for the detailed explanation….very much appreciated.
Ranga 2014-05-22 19:53:28
i managed to fix the problem and removed the inequality operation and changed the query to do a equality operation still CL seek, reads reduced to 500 from 17k, Awesome! thanks for your tip.