#SQLServer Checktable OLEDB Waits Part IV - how bow dah?

OLEDB Waits in #SQLServer checktable Part I - Disappearing Act

http://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

http://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?!

http://sql-sasquatch.blogspot.com/2017/06/checktable-oledb-waits-part-iii-more.html

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!

CREATE TABLE OLEDB_TEST_HEAP_NCI
([num] BIGINT INDEX nci_OLEDB_TEST_HEAP_NCI NONCLUSTERED,
 [str] NVARCHAR(4000)
 );

DECLARE @str NVARCHAR(max);
SET @str = REPLICATE ('A', 4000);

;WITH cnum(num) AS
(SELECT TOP (80) 1 FROM sys.columns)
INSERT INTO OLEDB_TEST_HEAP_NCI
SELECT row_number() OVER (ORDER BY (SELECT NULL)),
@str
FROM cnum c1
CROSS JOIN cnum c2;

EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 8, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 7, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 6, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 5, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 4, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 3, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 2, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 1, @PhysicalOnly = 'Y', @TABLOCK = 'N';

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.

EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 8, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 7, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 6, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 5, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 4, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 3, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 2, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI', @MAXDOP = 1, @PhysicalOnly = 'N', @TABLOCK = 'N';

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.

CREATE TABLE OLEDB_TEST_HEAP_NCI_INCLUDE
([num] BIGINT,
 [str] NVARCHAR(4000)
 );
CREATE INDEX nci_OLEDB_TEST_HEAP_NCI_INCLUDE ON OLEDB_TEST_HEAP_NCI_INCLUDE(num) INCLUDE([str]);

DECLARE @str NVARCHAR(max);
SET @str = REPLICATE ('A', 4000);

;WITH cnum(num) AS
(SELECT TOP (80) 1 FROM sys.columns)
INSERT INTO OLEDB_TEST_HEAP_NCI_INCLUDE
SELECT row_number() OVER (ORDER BY (SELECT NULL)),
@str
FROM cnum c1
CROSS JOIN cnum c2;

How many pages are we using? 12865.

Let's proceed with 8 physical_only checktable operations.

EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 8, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 7, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 6, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 5, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 4, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 3, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 2, @PhysicalOnly = 'Y', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 1, @PhysicalOnly = 'Y', @TABLOCK = 'N';

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.

EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 8, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 7, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 6, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 5, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 4, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 3, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 2, @PhysicalOnly = 'N', @TABLOCK = 'N';
EXEC CHECKTABLE_OLEDB_WAIT_TEST @tableName = N'OLEDB_TEST_HEAP_NCI_INCLUDE', @MAXDOP = 1, @PhysicalOnly = 'N', @TABLOCK = 'N';

And there's that really large jump in OLEDB wait events as the checktable operation drops the physical_only operation.