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.

Related Posts

Remote DAC And Vulnerability Assessments

Max Vernon points out a SQL Server Management Studio Vulnerability Assessment check which seems somewhat incomplete: Certainly, you’d want to ensure the port for the DAC is not available to the Internet, but hopefully if you’re reading this blog you already know how silly it would be to open SQL Server to the Internet. Assuming […]

Read More

Handling MAXDOP On Azure SQL Database

Arun Sirpal plays with MAXDOP settings on Azure SQL Database: Can we change it? No. EXEC sp_configure 'cost threshold for parallelism', 10; GO RECONFIGURE; GO Msg 2812, Level 16, State 62, Line 9 Could not find stored procedure ‘sp_configure’. Msg 40510, Level 16, State 1, Line 11 Statement ‘CONFIG’ is not supported in this version of SQL Server. […]

Read More


June 2017
« May Jul »