0

I'm inserting a batch of rows - using INSERT INTO tblUploadBanking SELECT FROM ... - into a table, which is a heap and have 3 narrow NC indexes.

When I execute the query from SSMS with SET STATISTICS IO ON, I can see output like this:

Table 'tblUploadBanking'. Scan count 0, logical reads 709738, physical reads 493, read-ahead reads 429, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 3, logical reads 136234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tblTrans_Cash_HO'. Scan count 1, logical reads 96944, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tblPaymentType'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tblWorkstation_Group'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When I sum the number of logical reads, I'm getting 942922 reads. But, when I look into the output from profiler, I can see for this query 1475067 logical reads, which is 532k more than the sum from statistics output. BTW, from Extended Events I'm getting same number like from profiler.

What are these hidden reads? The only thing I have found out so far is that there is a connection with heap and indexes fragmentation. When I rebuild the heap, the execution plan remains exactly the same - including cost distribution, rows estimations etc. The number of reads in statistics is about the same as well, but in the profiler the number of logical reads has dropped to 1007183, which is almost matching with the output from statistics. Also the CPU time has reduced by 25%.

With other queries I had even bigger discrepancies - for few sessions I saw in trace 6-10M reads, but according to statistics there were just about 900k of logical reads. These queries were also inserts into fragmented heap and rebuilding the heap "solved it" as well like in this case.

From my knowledge about the heaps, there shouldn't be any reason for slower inserts when the heap is fragmented. It could be because of fragmented NC indexes, but I would expect that logical reads needed for index insert are included in statistics for the given table.

PS: Please do not suggest me to turn table from heap to clustered table, it's a plan for the future which is not possible now.

  • Is the heap empty when you insert into it? Are you deleting any data from it? – Erik Darling Jul 5 '17 at 1:20
  • There are about 4M of rows. There were some deletes after last heap rebuild, but not in the session when I see the difference. – Lukas.Navratil Jul 5 '17 at 1:59
  • What's the insert query doing? – Erik Darling Jul 5 '17 at 2:12
  • It's inserting data from select over tblTrans_Cash_HO table joined to tblPaymentType and tblWorkstation_Group tables. Quite simple query. Previously I had the same issue when I was running just INSERT INTO staging_table SELECT * FROM #temp_table where #temp_table was created using SELECT * INTO #temp_table FROM staging_table. So it does not depend on underlying query. BTW, there are no triggers on the table and no FKs to or from any other table. – Lukas.Navratil Jul 5 '17 at 2:26
2

When inserting into a table without specifying TABLOCK hint the operation is fully logged (every row insert is logged) and the number of logical reads reported by query statistics is roughly equal to the number of inserted rows; so it's the same in case of fragmented/not fragmented heap (1 insert = 1 destination table page access), but what is not counted here is PFS-pages access, and the Profiler does count it as logical reads.

When you insert into rebuilt heap, there is no need to try to insert in any existing page because they are all full, but when inserting to a fragmented heap there is free space in the heap and it should be checked for every row inserted (where it's possible to insert it).

IMHO, the fact that there is no more need to search in PFS pages is revealed at the first attempt to find a free space in the existing pages when all pages allocated for this heap are checked without success for a rebuilt heap, and the number of possible accesses the PFS pages grows as the fragmentation grows. As soon as the searching for free space failes when trying to insert the Nth row, for the remaining rows it allocates new pages directly

  • Your answer definitely makes sense, but still the overhead seems too big to me. Before the rebuild of the heap, it was stored on 117204 pages. One PFS page holds information for 8,088 pages, so there should be about 15 PFS pages for this table. I was inserting 66000 of rows, it would mean overhead of reading 8 PFS pages per single row. I don't believe that the internal implementation can be so inefficient... For another query with bigger overhead it would mean reading all PFS pages for every inserted row... – Lukas.Navratil Jul 6 '17 at 22:49
  • It accesses PFS page, even one only PFS page, as many times as it need to search for free space. Do you remember how lookups work? You can have all the lookup values living on the same page, but every lookup costs you one logical read, and here we have the same thing. You said you have CPU increased in case of fragmented heap, it's a cost of interpreting a PFS page every time it's accessed – sepupic Jul 7 '17 at 4:07
  • I believe that it is possible to generate so many reads, but the algorithm for space allocation would have to be quite naive. If it needs to read half of existing PFS pages for every record from the batch (=single select on input), it means that it is probably scanning PFS pages where there is no space for new records again and again. Ok, there can come smaller record that can fit to some existing page or some space could have been released in the meantime, but I would still rather to ignore these pages and allocate new ones, it would be much more efficient... – Lukas.Navratil Jul 7 '17 at 4:30
  • Maybe my English is so bad that I cannot explain, but I'll try one more time: for example, I insert 1000 rows without nolock and I have a heap that is empty enough to accomodate my 1000 rows, so this insert will generate 2000 logical reads: 1000 for every row inserted, counted by statistics, and 1000 other reads, they are all reads of 1 single PFS page(assuming my heap has nearly 8000 pages. For every of 1000 records server re-read this PFS page, at every insert – sepupic Jul 7 '17 at 12:50
  • Continue: now assume I rebuilt my heap prior to this insert, in this case I will have only 1001 reads: 1000 for every row insert and 1 for PFS page read: at the first row insert server searches for empty space and read related PFS pages, 1 page for my example, it reads all the bytes because it cannot find empty space, at this point it stops: for remaining 999 rows it does not go to PFS pages anymore because for this insert statement it's failed at previous step, so there is no space in the heap and this time profiler will show 1001 reads, not 2000 – sepupic Jul 7 '17 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.