OLEDB waits in #SQLServer checktable Part I - Disappearing Acthttp://sql-sasquatch.blogspot.com/2017/06/oledb-waits-in-sqlserver-checktable.html
OLEDB waits in #SQLServer Checktable Part II - the song remains very nearly the same
OK. We've seen some stuff with a simple heap. Let's trade in the heap for a clustered index with the same row values.
The clustered index uses a few more pages - that makes sense. More structure to maintain for a clustered index than for a heap.
So let's perform the physical_only checktable operations on OLEDB_TEST_CLINDEX. Based on the previous results, we'll expect the number of OLEDB wait events to be close to the number of pages in the clustered index. And we expect OLEDB session waits to not update for MAXDOP 2 or 1, even though instance level OLEDB waits will update.
How do the results match our expectations? Pretty well. OLEDB_TEST_HEAP used 6408 pages, and OLEDB waits started at 6428 for MAXDOP 1 and MAXDOP 2, increasing by one for each increase in MAXDOP from 3 to 8. OLEDB_TEST_CLINDEX used 6438 pages, and OLEDB waits start at 6458 for MAXDOP 1 and 2, and increase by one for each increase in MAXDOP from 3 to 8.
All right. Let's move on to removing the physical_only parameter. With OLEDB_TEST_HEAP we saw a minimal change in the number of OLEDB wait events between a checktable with and without physical_only.
So how do the results match with expectations? Whoa!! 25745 OLEDB wait events as the starting point for MAXDOP 1 and 2. Then single-stepping up to 25752 OLEDB wait events for MAXDOP 8. 25752 is exactly 4 times the used page count of 6438 for the clustered index. So the OLEDB wait events went from a small number more than the count of pages/rows with the heap... to almost 4 times as many with the clustered index. *That* was a bit unexpected.
Continue with this thrilling tale here...
#SQLServer Checktable OLEDB Waits Part IV - how bow dah?