INTERSECT bug?

JamesB 2016-04-27 17:04:00

I am submitting this on behalf of Brian Cecil:

This may be more of an academic exercise than anything. The solution seemed to be adding an index to the zsuspense table.

I found the attached interesting. Not sure if it's a bug in the SQL optimizer, the INTERSECT operator implementation, or what's going on, but it took about 6 hours to generate the attached plan.

Note the number of reads. I don't see where those reads manifest themselves in the plan diagram or the plan tree. Are they hidden?

9348575 rows in zSuspense

8682197 rows in MatchMade_Main

1604438 rows in MatchMade_Details

Aaron Bertrand 2016-04-27 17:49:19
Mostly, this confusion occurs because reads captured through trace/extended events can includes many things that STATISTICS IO does not. A small list is available in the rather old KB #314648. I don't know of an updated list, but I'm sure it's a bigger one in more modern versions of SQL Server, and that generally the same principal holds true:

Reads and STATISTICS IO are not necessarily apple-to-apple comparisons.

Is it possible, for example, that the query triggered an autostats? That would be a potentially high number of reads attributed to your batch, but not accounted for in the statement you're looking at (and if you captured the plan in Plan Explorer FREE, the STATMAN call would not show up in the statement tree/query call stack).