(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
This wait type is when a thread is waiting for data from an OLE DB Provider, which is used internally for things like DBCC CHECK* commands and DMVs, and externally for things like linked server communication and some SSIS packages. Note that this is a preemptive wait, so the thread will remain on the SQL Server scheduler until the OLE DB operation completes and will not yield.
(Books Online description: “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.”)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
Pre-2005/2005
Removed in SQL Server version:
N/A
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 109 in 2008 and 2008 R2, and 113 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
The knee-jerk response for this wait type is that ‘it must be linked servers’. However, there has been a proliferation of usage of third-party performance monitoring tools in recent years which has resulted in the OLEDB wait type becoming more prevalent as a top wait on people’s servers. This is because these monitoring tools using DMVs repeatedly, and many DMVs use OLE DB under the covers.
My general guidance is that if the OLEDB waits on your server are just a few milliseconds or less on average, and there are millions or billions of them, it’s likely to be a performance monitoring tool and these waits are just part of your baseline. Lots of small waits could also be DBCC CHECK* commands. If the waits are many tens or hundreds of milliseconds or more, then it’s more likely to be something like an SSIS package or a linked server. If you have linked servers and you can correlate the long OLEDB waits with queries to one of them, do performance troubleshooting on the linked server.
Note that there is a known issue since SQL Server 2000 with rolled-back operations through linked servers, where the linked server is not SQL Server. It seems that sometimes if the remote system does not terminate the link properly, the SQL Server connection remains open, showing OLEDB as the wait type, and the only way to solve the problem is to restart SQL Server.
Known occurrences in SQL Server (list number matches call stack list):
- Waiting for a row to be sent through OLE DB
- Waiting for a row to be sent through OLE DB (in this case, as part of the communication between the Storage Engine and Query Processor portions of DBCC CHECKDB)
And other similar call stacks.
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PopWait+b1
CAutoOledbWait::~CAutoOledbWait+ae
CQScanRmtScanNew::GetRowHelper+3c3
CQScanXProducerNew::GetRowHelper+366
CQScanXProducerNew::GetRow+15
FnProducerOpen+57
FnProducerThread+8c3
SubprocEntrypoint+a7f
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+ab - SOS_Task::PopWait+b1
CAutoOledbWait::~CAutoOledbWait+ae
CQScanRmtScanNew::GetRowHelper+3c3
CQScanSortNew::BuildSortTable+246
CQScanSortNew::OpenHelper+c0
CQScanNew::OpenHelper+41
CQScanStreamAggregateNew::Open+30
CQueryScan::StartupQuery+23c
CXStmtQuery::SetupQueryScanAndExpression+2e5
CXStmtQuery::InitForExecute+34
CXStmtQuery::ErsqExecuteQuery+36d
CXStmtSelect::XretExecute+2e7
CMsqlExecContext::ExecuteStmts<1,1>+427
CMsqlExecContext::FExecute+a43
CSQLSource::Execute+86c
CSQLSource::SeExecute+119
ExecSql+4e2
ExecSql+c6
CExecSql::Execute+35
CheckAggregateSingleInstance::Verify+1bf
CheckTables::Verify+298
CheckTables::Check+6c1