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 samehttp://sql-sasquatch.blogspot.com/2017/06/oledb-waits-in-sqlserver-checktable_5.html
Checktable OLEDB Waits Part III - the more things change, the more... wait, WAT?!
OK. Through the first 3 parts of this epic saga we've taken a glance at checktable operations on a simple heap(with no indexes), a simple clustered index (no additional indexes). Let's take a look at a heap with a nonclustered index!
The results below look vaguely familiar. 6456 total pages used. Starting at 6464 OLEDB wait events for maxdop 1 and 2. Up to 6471 OLEDB wait events at maxdop 8. In the image below I've also circled a few of the OLEDB wait_ms numbers from system and session level wait DMVs. Circled because today is the first day I've noticed differences in the numbers - although I've yet to see a difference greater than 1 ms. Still - as far as I'm concerned even a difference of 1 ms is a hint that *maybe* session and system level waits are tracked separately rather than a single source that updates both of the views.
Now - if we change to running without physical_only, will we get some multiple of the table row count, since there is a nonclustered index? Recall that the clustered index sustained nearly four times the row/page count as the number of OLEDB waits in a checktable without physical_only.
Nope. Just a small incremental additional number of OLEDB wait events, starting at 6519 and working up to 6526. Huh.
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.
How many pages are we using? 12865.
Let's proceed with 8 physical_only checktable operations.
How's it look? Pretty familiar. 12865 pages in use, a small overhead gets us 12909 to 12916 OLEDB wait events.
Ok, now 8 checktable operations without the physical_only option.
And there's that really large jump in OLEDB wait events as the checktable operation drops the physical_only operation.