Monday, June 5, 2017

OLEDB waits in #SQLServer Checktable Part II - the song remains very nearly the same

In Part I, the main focuse was on OLEDB waits in a checktable with physical_only on a heap table with 6400 rows, at 1 row per 8k page.  Part II is gonna be pretty brief - but there's more coming I promise!!

OLEDB waits in #SQLServer checktable [part I] - Disappearing Act
http://sql-sasquatch.blogspot.com/2017/06/oledb-waits-in-sqlserver-checktable.html

What happens if the logical checks are included in the checktable operation on this table, by removing the physical_only option?


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

In this case, not very much.


In comparison to the physical_only checktable operation, at each DOP the number of OLEDB wait events increased by one.  Maxdop 2 and 1 still have the same number of OLEDB wait events, while from maxdop 3 to 8 the number of wait events increases by one each time maxdop increases by one.  And as was seen previously, at maxdop 2 and maxdop 1 the session waits DMV isn't catching the OLEDB wait activity associated with the checktable operation, even though it is being captured at the system level.

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.

[
carry on with this epic saga here...
Checktable OLEDB Waits Part III - the more things change, the more... wait, WAT?!
]
 


No comments:

Post a Comment