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

SQL WAIT TYPE – OLEDB and how to reduce it

SQL Server Books online defines the OLEDB wait stat type as “Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider”

Examples of where OLEDB wait type may occur is on an application replication. An application may connect to the SQL Server database – and issue a client side replication whereby millions of rows are replicated row by row.   The client may have to traverse a  below par performing network path.  

Other examples include: BULK INSERT, search queries, queries using linked servers.

OLEDB is an API that extends the data store into the application. Data source, rowsets, commands and session are abstracted. The application provides data and is consumed by the database , without the application having to deal with technology platform specifics.

 Troubleshooting the OLEDB wait type

1)  OLEDB wait type suggests SQL Server is waiting for a resource outside of SQL Server.  For example , an Excel spreadsheet on a desktop. Identify the application and focus on tuning the application.

2)      Locate the client application . Is it following a low performing network path?

3)      Analyse the other wait types , such as RESOURCE SEMAPHORE .

 

Comments

Posted by Robert Pearl on 17 October 2011

In addition, this wait type also occurs when Link Server or Remove Query has been executed. The most common case wherein this wait type is visible is during the execution of Linked Server.

When SQL Server is retrieving data from the remote server, it uses OLEDB API to retrieve the data. It is possible that the remote system is not quick enough or the connection between them is not fast enough, leading SQL Server to wait for the result’s return from the remote (or external) server.

- RP

Leave a Comment

Please register or log in to leave a comment.