Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SQLSailor - Personal Notes on Microsoft SQL Server

Browse by Tags · View All
SQL Server 2012 35
General 13
News/Updates 10
Windows Azure 7
HA Solutions 7
Cool Features 4
Connect Items 4
SQL Azure 4
Powershell 4
PASS 3

Archive · View All
January 2012 17
April 2012 11
March 2012 11
June 2012 9
May 2012 9
August 2012 6
September 2012 5
July 2012 5
March 2013 4
February 2013 3

Anup Varier's Blog

OLEDB Wait and DBCC – An interesting discussion

Apr 5 2012 12:00AM by Anup Warrier   

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.


Republished from SQLSailor [47 clicks].  Read the original version here [5 clicks].

Anup Warrier
209 · 1% · 224
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"OLEDB Wait and DBCC – An interesting discussion" rated 5 out of 5 by 1 readers
OLEDB Wait and DBCC – An interesting discussion , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]