How can this query show > 10M reads?

I have one fairly simple Insert query. Looking at the data below, I cannot undesrstand why the results show > 10,000,000 reads! (I really hope that didn't happen!!)

 Statement    Est Cost    Duration    CPU    Est CPU Cost    Reads    Writes    Est IO Cost    Est Rows    Actual Rows    EndTime    Degree of Parallelism    Parallel Operations    Key Lookups    Clustered Index Scan Operations    Sort Operations    Hash Match Operations    Index Scan Operations    Table Scan Operations    StartTime
 INSERT [Cert].[Certificate] ( PortfolioFeedID ,BatchControlID ) OUTPUT inserted.[ParentCertificateID] INTO #NewParentCertificates SELECT @PortfolioFeedID, BatchControlID FROM #NewImportRowID -- Create mapping between ImportId and newly generated ParentCertificateIDs for increased performance ------------------------------------------------------------     22.2%    83,363    32,984    6.3%    10,984,619    14,026    29.5%    2,451,640    2,451,638    2016-09-19 12:41:30.077    1    0    0    1    0    0    0    0    2016-09-19 12:40:06.713

Plan.pesession (469.2 kB)
avatar image By gbritton 93 asked Sep 19, 2016 at 04:51 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Sep 19, 2016 at 04:54 PM

This is what Profiler would have told us. Did you run the query in Profiler and compare? Or in Management Studio with SET STATISTICS IO ON?

10|10000 characters needed characters left

1 answer: sort voted first

It's certainly possible, especially if the table being inserted to had a large number of rows before the insert (several b-tree levels).

Logical reads are reported for non-minimally-logged inserts when SQL Server navigates down the b-tree to locate the insert point, and/or to read output values, depending on the exact scenario. Where multiple levels of b-tree are navigated per row inserted, the number may indeed exceed the number of rows. Extra reads may also be associated with prefetching activity.

The information reported by PE should be the same as given in SET STATISTICS IO output, since that's where the data comes from.

avatar image By SQLkiwi ♦ 6.6k answered Sep 19, 2016 at 05:39 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.