Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DotNine SQL Server and more

I am a database specialist from the Netherland with my main focus on Microsoft SQL Server. My IT career began 14 years ago as a System Administrator. I have been working with SQL Server for more then 10 years now and a full time DBA for 5 years.

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:

1
2
3
4
5
6
7
8
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'
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:

oledb_01

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:

1
2
3
4
5
6
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
 
DBCC CHECKDB('master')>
 
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'OLEDB'
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:

oledb_02

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.

Comments

Leave a comment on the original post [www.dotnine.nl, opens in a new window]

Loading comments...