Table in exec plan but not in STATISTICS IO results

Srdjan Milakovic 2014-07-23 19:05:08

I have an Execution plan which shows that 447 thousand actual number of rows are being read from a table but the table is not listed in the STATISTICS IO output. I am using SQL Server 2008 SP3CUM4 and the SQL engine is generating a parallel execution plan. When I execute the query using OPTION (MAXDOP 1), the table shows up in the STATISTICS IO output. I am aware of the Connect entry "STATISTICS IO under-reports logical reads for parallel plans" reported by Aaron Bertrand. But, I think, that this is not the same issue.
The query is not that simple and I am not able to reproduce the issue in a publicly available database.

Did anyone experience something similar? Why would the STATISTICS IO report skip this table?

Thank you.

Aaron Bertrand 2014-07-23 19:08:16
Are you seeing this issue in Management Studio? What if you generate an actual plan in Plan Explorer? I agree that this is not the same issue that I reported (which still reported the table, just fewer reads than actual), but I haven't seen scenarios like yours yet.
Srdjan Milakovic 2014-07-23 19:22:27
Plan Explorer does not show the Table on the Table I/O tab. Even though you can see that the Index Seek against that table in the plan costs 8% of the whole query.
Aaron Bertrand 2014-07-23 19:25:19
Can you post a plan? Or share it with me at abertrand at sqlsentry dot com? I'd like to inspect to see if it's in the XML but omitted for some reason, or if it simply never makes it to the XML. Does the table have any weird characters in it, for example? Could be a Unicode issue or an XML entity issue…
SQLkiwi 2014-07-23 21:31:31
If rows are definitely read from the table but not reported by STATS IO, this would be a bug. Having an estimated cost of 8% is not proof the table is read, by the way.

You should contact CSS. I expect there would be quite a high bar to meet for a hotfix on 2008, unless it also reproduces in a current version. If you're in a position to pay for the hotfix, that might be different 🙂

Srdjan Milakovic 2014-07-23 21:56:47
The table does not have XML or Unicode columns. It does not have weird characters in it. The result set is being filtered against that table using AND NOT EXISTS (SELECT * FROM TableInQuestion…). I will post more information if we decide to pursue this issue or if we manage to reproduce it on SS 2014.

Thank you for the feedback.

Srdjan Milakovic 2014-07-24 17:41:32
Attached you can see the obfuscated plan generated by SQL Sentry Plan Explorer. Please note that there are 447799 rows flowing from Table7 but it does not show up in the Table I/O tab.

ObfuscatedPlan

SQLkiwi 2014-07-24 21:41:17
Apply the latest Cumulative Update 17 for SQL Server 2008 SP3 (you are currently on CU4). If the issue remains, it is almost certainly a bug, for which you should open a case with Microsoft Support Services. It may well be that the underlying issue has already been corrected in a later version of SQL Server, but not back-ported to 2008.

SQL Server 2008 is out of mainstream support now, but there will be 12 months of normal support for 2008 SP4 when it is released.

Srdjan Milakovic 2014-09-04 21:50:10
We managed to test the same script on SQL Server 2014 CU2. We were not able to reproduce the bug. We will not escalate this to Microsoft since we have plans to migrate from SS 2008 in the near future.

Thank you for your replies.