Checktable OLEDB Waits Part III - the more things change, the more... wait, WAT?!

Third part of an adventure in #SQLServer Checktable.

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

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.

CREATE TABLE OLEDB_TEST_CLINDEX
([num] BIGINT,
 [str] NVARCHAR(4000)
 constraint pk_OLEDB_TEST_CLINDEX primary key clustered(num));

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

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

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. 

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

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.

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

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?

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

]