SQL SERVER – OLEDB – Link Server – Wait Type – Day 23 of 28

When I decided to start writing about this wait type, the very first question that came to my mind was, “What does ‘OLEDB’ stand for?” A quick search on Wikipedia tells me that OLEDB means Object Linking and Embedding Database. (How many of you knew this?) Anyway, I found it very interesting that this wait type was in one of the top 10 wait types in many of the systems I have come across in my performance tuning experience.

Books On-Line:

OLEDB 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.

Solarwinds

OLEDB Explanation:

This wait type primarily happens 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. This is the time OLEDB wait type occurs.

Reducing OLEDB wait:

  • Check the Link Server configuration.
  • Checking Disk-Related Perfmon Counters
    • Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
    • Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
    • Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

At this point in time, I am not able to think of any more ways on reducing this wait type. Do you have any opinion about this subject? Please share it here and I will share your comment with the rest of the Community, and of course, with due credit unto you.

Please read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28
Next Post
SQL SERVER – 2000 – DBCC SQLPERF(waitstats) – Wait Type – Day 24 of 28

Related Posts

8 Comments. Leave new

  • possibly its REMOTE QUERY…(Remove Query P2, L1)

    Reply
  • I see it on one of our servers which was rebooted 3 days ago, with a wait time of 20530 seconds, which is quite a lot.

    This is a SQL server in which big financial transactions are loaded every day to scan them for fraud. It seems indeed to go together with Buffer IO high wait types, which account for 17% of total system waits compared to the OLEDB wait (5,05%).

    Reply
  • Jules Winnfield
    April 19, 2011 3:40 pm

    I see this waittype a lot, if I run SQL Traces (SQL Server 2008).

    I’m not able to find any informations about wich spid is involved in an OLEDB wait (i.e. in sys.dm_os_waiting_tasks).

    The only way is to quit the SQL Traces and check the wait_stats again.

    Do you now how to find the spids involved in OLEDB wait?

    Reply
    • ALICIA TRINIDAD CANTERO LARROZA
      January 29, 2016 3:28 am

      You can use the next query:
      SELECT p.SPID, Blocked_By = p.Blocked, p.Status, p.LogiName, p.HostName,
      Program = coalesce(‘Job: ‘ + j.name, p.program_name), DBName = db_name(p.dbid),
      Command = p.cmd, CPUTime = p.cpu, DiskIO = p.physical_io, LastBatch = p.Last_Batch,
      LastQuery = coalesce((select [text]
      from sys.dm_exec_sql_text(p.sql_handle)),”), p.WaitTime, p.LastWaitType, LoginTime = p.Login_Time, RunDate = GetDate(),
      [Server] = serverproperty(‘machinename’), [Duration(s)] = datediff(second, p.last_batch, getdate())
      FROM master..sysprocesses p
      left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(sys.fn_varbintohexstr(j.job_id),3,100)
      where p.spid > 50 and p.status ‘sleeping’ and p.spid @@spid order by p.spid

      Reply
  • Hi, i have question. Why if i run “select * from linked_server.master.dbo.syslogins”
    i get ROLLBACK TRANSACTION in Profiler?

    Reply
  • I mention that i don’t have any logon trigger’s on server and i get the result of the select.

    Reply
  • Allen McGuire
    June 6, 2012 11:40 pm

    I believe my high OLEDB wait time is due to OpenXML calls, which show up in the execution plan as remote scans, and they are very slow.

    Reply
  • prince rastogi
    March 20, 2013 9:18 pm

    Hi pinal, This may be due to the more use of DMVs, because DMVs also use OLEDB for internal use.

    Reply

Leave a Reply

Menu