OLEDB Wait and DBCC – An interesting discussion


Yesterday there was an interesting discussion related to OLEDB wait type getting spiked when DBCC is executed.

This was pretty interesting question for me and was curious to really understand if OLEDB wait type has a dependency with DBCC.

OLEDB is a common wait type in SQL Server.There was a survey conducted by the well know SQL Server Expert and Master Paul S Randal(Blog/Twitter) 2 years back and OLEDB was one of the top 10 waits found across multiple environments.

This wait type indicates that an SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This wait type may also indicate that the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands or full-search queries.

We inquired with the person who posted the question about OLEDB wait and DBCC relation if he was running any linked servers and the answer was NO.He confirmed that there are no queries which are running while the DBCC was running.There are some DMV’s which might be triggering OLEDB,however as there was no other queries were running it was obvious to check if DBCC really uses OLEDB internally.

The short answer to confirm the relation is,Yes! DBCC uses OLEDB internally and a small spike is expected.

Its normal to get a spike in OLEDB wait,however the average wait_time will be relatively less when you compare this wait with other waits.

If you are using linked servers,full text search and observing OLEDB as a potential candidate of wait type,then you will need to trouble shoot and isolate the issue.

Below is a quick demo to show a increase in this wait type when you run DBCC.

1. Below is the Wait Stats before running DBCC

2. We will run DBCC for Adventureworks 2012 database

3. Below is Wait Stats after running DBCC

As you see there is a small spike in the values and you could even run DBCC on a huge database and see how much spike you are experiancing.

Conclusion

If DBCC is running fine and is not running long duration than expected,then this wait type is not necessarly a cause for concern.

I am interested to understand if you are facing any issues with this OLEDB wait type in your environments and what course of action you have done.

Thanks for reading.

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s