SQL Server OLEDB Wait Type

OLEDB is a common wait type in SQL Server, indicating a significant amout of wait time accumulation. 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.

A closer look at Microsoft OLE DB

OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for uniformly accessing different types of data. This set of interfaces is implemented using the Component Object Model (COM); and is otherwise unrelated to OLE. It was designed as a higher-level replacement for, and successor to, ODBC, to allow extension of features to non-relational databases and spreadsheets that are not SQL-based.

OLEDB separates the data store from the application that needs access to it through abstractions including the datasource, session, command and rowsets. The approach was taken since different applications need access to varying types and sources of data and do not necessarily require knowledge of technology-specific methods.

OLE DB is conceptually divided into consumers and providers. In this case, SQL Server is the consumer of the data and the provider is the software component that implements the interface and therefore provides the data to the consumer.

OLE DB is part of the Microsoft Data Access Components (MDAC) stack, a group of Microsoft technologies that interact together as a framework allowing programmers a consistent way of developing applications for accessing almost any data store. OLE DB providers can be created to access such simple data stores as a text file and spreadsheet, through to such complex databases as Oracle, SQL Server and Sybase ASE. It can also provide access to hierarchial datastores such as email systems.

When the SQL Server database is waiting for an external system to respond to an OLEDB request, the OLEDB wait type is the result.

Fixing the OLEDB wait type

The wait on OLEDB tells us (for the most part) the query is waiting on something outside of the SQL instance. Tuning the query will involve tuning what is being accessed. If it is another database, tune the query that results inside the other database. If it is an excel spreadsheet, then tune the spreadsheet (if you can :-)). At least if you see this wait, you know that you’re waiting on something external to the database. Try one of the following approaches:

1. Check placement of client application, including any file input read by the client and SQL Server data and log files. See Disk secs/Read and Disk secs/Write. If Disk secs/Read is high, add additional I/O bandwidth, balance I/O across other drives, or put the database and transaction log on its own drives. Inspect Transact-SQL code for RPC, Distributed (Linked Server), and Full Text Search. Although SQL Server supports these kinds of queries, they sometimes cause bottlenecks.
2. In addition, if you are getting excessive wait times on OLEDB, try tuning the other wait types that are occurring at the same time. For example, if you are having OLEDB waits and RESOURCE_SEMAPHORE waits, review the amount of memory being used either by concurrent requests or bad queries.