Examining OLEDB waits
On our adventure through the various types of wait statistics we end up at another popular wait type: OLEDB.
In this article we will take a closer look at OLEDB waits to help you understand where they come from.
OLEDB
OLEDB or, Object Linking and Embedding DataBase, is a Microsoft COM interface to access data.
Microsoft describes OLEDB on MSDN as follows:
“OLE DB is a set of COM-based interfaces that expose data from a variety of sources. OLE DB interfaces provide applications with uniform access to data stored in diverse information sources, or data stores.”
Generally we will see OLEDB waits when we make a call to an OLE DB provider and we are waiting on our data.
Generating OLEDB waits
OLEDB waits happen a lot in environments that use linked servers (there are some exceptions, we’ll get into one specific one later in this article).
In this example I created a linked server configuration between 2 instances on my test server.
The first thing to do is to clear the sys.dm_os_wait_stats DMV, directly after that I will execute a query to the linked server, after that we will take a look at our wait statistics for the OLEDB wait:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
SELECT *
FROM [EVDL-SQL2012-03].[AdventureWorks2012].[Sales].[SalesOrderDetail]
GO
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'OLEDB'
These where the results on my server:
As you can see querying a linked server will result in OLEDB waits, if you have to option of not using linked servers to access data it is an easy way to lower OLEDB waits.
This is a pretty simple example and there are a lot more sources of OLEDB waits like Excel data sources, BULK IMPORT, applications that use OLEDB, etc.
There is a more surprising one however!
DBCC
One of the more surprising ways to generate OLEDB waits is by using DBCC commands like DBCC CHECKDB() !
We can also test this pretty easy by running the query below:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
DBCC CHECKDB('master')>
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'OLEDB'
We will first clear our wait statistics, run a CHECKDB on the master database and check the wait statistics for OLEDB again.
In my case I got this:
Lowering OLEDB waits
Now that we have seen some ways to generate OLEDB waits let’s discuss how we can lower the wait time for this wait type.
Since a lot of OLEDB waits tend to happen when you are using linked servers, minimizing the use of linked server will make the OLEDB wait time go down.
Netwerk performance problems can also cause higher wait times when using OLEDB with an application or linked server.
Also keep an eye out on your CHECKDB runs, as you have seen in the example above DBCC commands use OLEDB under the hood making OLEDB wait times higher when running a CHECKDB for instance.
19 comments to “Examining OLEDB waits”
I go to see each day a few web pages and blogs to read
posts, but this weblog offers feature based articles.
I’m amazed, I have to admit. Rarely do I come across a blog that’s both equally educative
and interesting, and without a doubt, you’ve hit the nail on the
head. The issue is an issue that too few folks are speaking intelligently about.
I am very happy I found this in my search for something concerning this.
I know this site provides quality depending posts and extra data, is there any other web page which provides
these kinds of things in quality?
Simply wish to say your article is as surprising.
The clearness in your post is simply cool and i could assume you are an expert on this subject.
Fine with your permission allow me to grab your RSS feed to keep updated with
forthcoming post. Thanks a million and please continue the enjoyable work.
Also has a land-primarily based presence running sportsbooks in Mississippi.
It’s not my first time to pay a visit this web page, i am visiting this
web page dailly and take good information from here everyday.
Excellent article! We will be linking to this great content on our website.
Keep up the good writing.
It’s remarkable to pay a quick visit this web page and reading the views
of all friends about this piece of writing, while I am also eager of getting know-how.
This article will help the internet viewers for setting up
new website or even a weblog from start to end.
Very nice blog post. I certainly appreciate this site.
Stick with it!
An outstanding share! I’ve just forwarded this onto a friend who had been doing a little homework on this.
And he actually ordered me dinner simply because I discovered it
for him… lol. So allow me to reword this…. Thanks for the meal!!
But yeah, thanx for spending some time to talk about this subject here on your internet site.
Hi, every time i used to check web site posts here
early in the break of day, for the reason that i like to find out more and more.
It’s really a cool and useful piece of information. I am happy that you
just shared this useful information with us.
Please keep us informed like this. Thank
you for sharing.
Back-up Staffing PlanMonitor absenteeism of coaches
and officials, cross-train staff, and generate a roster of educated back-up
personnel.
“But we are a bit concerned about implementing that into the law.
Sports betting is legal in the state at all registered land-primarily based and riverboat sportsbooks.
If you desire to get much from this piece of writing then you have to apply these strategies to your
won website.
Great site you’ve got here.. It’s difficult to find high-quality writing like yours nowadays.
I seriously appreciate people like you! Take care!!
This article will help the internet viewers for creating new blog or even a blog from start
to end.