OLE DB wait events in SQL Server

Introduction to OLE DB Wait events

The OLE DB wait type is one of the most common wait types in a SQL SERVER database. It means that a session (SPID) has a call request through SQL Server Native Client OLE DB provider and waits for the database to return the required data. It appears in the remote system or the network connection is not fast enough, so the calling server must wait for the result to be returned. What do OLE DB wait events typically do with those activities? It is generally caused by some of the following events:

1. Remote Procedure Call (remotes procedure calls)
2. Linked server queries (Linked server queries)
3. BULK INSERT Commands
4. Full-search queries or external data source processing, such as Excel
5. Profiler tracking for clients (Client-side Profiler traces)
6. DMV, Log reader and DBCC CHECKDB

It's strange to see DBCC CHECKDB cause an OLE DB wait event, but I was surprised when it started, and our DPA (Database performance Analyzer) monitoring tool found SQL 2012, SQL The OLE DB wait events caused by the DBCC CHECKDB of the 2014 database occupy a large proportion of the wait type histogram. This aroused my curiosity, after verifying that DBCC CHECKDB it uses OLE DB rowsets to exchange information between the query processor and the subsystem of the storage engine.

Here's an example of an OLE DB wait event appearing in DBCC CHECKDB:

--SESSION 1

DBCC SQLPERF (' sys.dm_os_wait_stats ', CLEAR);

GO

DBCC CHECKDB (' AdventureWorks2014 ');

--SESSION 2

SELECT  from

Sys.dm_os_wait_stats

WHERE wait_type=' OLE DB '

While DBCC CHECKDB is still executing, you will see in session 2 that the OLE DB wait type of Waiting_tasks_count, Wait_time_ms will always increase. With regard to the OLE DB wait events generated by DBCC CHECKDB, I observed from DPA that SQL Server 2012, SQL Server 2014 is significantly more than SQL Server 2005, SQL Server 2008, and for the time being unclear why, In SQL Server 2012/2014, DBCC CHECKDB has several bugs that can be referenced in the official documentation SQL Server Service Pack 1 release information. We can see that the official fix has dropped three bugs on DBCC CHECKDB. But I updated these patches and still found that DBCC CHECKDB caused more OLE DB wait events.

In addition, the DMV will cause OLE DB to wait for events because OLE DB is used internally by the DMV, so frequent calls to the DMV by some monitoring tools can also cause more OLE DB wait events to occur.

OLE DB waits for event resolution

1: From the application level, only the necessary fields and data are returned. Reduce network transmission of content and time. This reduces the OLE DB wait events. For example, paging control gets data by page.

2: If the data obtained using linked server is static data, you can consider copying these static data to the local database, reducing the unnecessary link server queries. If the data is dynamic, you can also consider using replication (publish subscription) to synchronize the data to the local database.

3: Review your data distribution from a business perspective, is it absolutely necessary to link server queries? What are those that can be reduced, those that are not necessary? In addition, the solution can be considered at the architectural level, such as centralizing those data.

4: If some data sources are Excel files, you can upload them to this computer via FTP or file share, and then process the data.

5: Optimize the SQL for the linked server query, check whether it is missing indexes, whether the statistics are outdated, whether some business logic can be placed on the remote server to process (stored procedures), and then return only the data that is needed. For example, the following requirement.

SELECT * FROM

Linked_server.dbo.table_a INNER JOIN:

Linked_server.dbo.table_b INNER JOIN:

Linked_server.dbo.table_c INNER JOIN:

.......................

6: Find the system administrator or network administrator to know the bandwidth of the leased line. Sometimes the network bandwidth resources are insufficient to cause the increase of OLE DB wait events.

OLE DB wait events in SQL Server