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 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!
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.