Lonny Niederstadt has dropped a boatload of information on us, with a four-part series looking at the OLEDB wait type when running DBCC CHECKTABLE. Part 1 sets up the problem:
So… yeah. Definitely a relationship between the number of pages in the heap and the number of OLEDB waits. Something(s) increase the number of OLEDB waits beyond the number of data pages, the number of used pages and the number of reserved pages in a way that I haven’t deciphered yet, though.
Part 2 looks at what happens when logical checks run and not just physical checks:
On this very simple heap, with nearly the same number of rows as pages in the heap, the number of OLEDB wait events is nearly the same – only increased by one – when “upgrading” the checktable operation from physical_only to a checktable with logical checks.
Part 3 switches to using a clustered index, and things start getting wacky:
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.
Part 4 looks at a heap with a non-clustered index:
All right. What if we use a heap with a nonclustered include index? Based on the table and index definition below, I expect the nonclustered include index nci_OLEDB_TEST_HEAP_NCI_INCLUDE to be remarkably similar to the clustered index we previously used.
Interesting reading so far in this series.