OLEDB waits: what they mean and a few ideas on addressing them

Typically when you see OLEDB waits they come from querying linked servers set up through OLEDB.  If you are seeing a high number of these as a query runs, you should consider monitoring performance on the remote server to see how the query on the local server splits the work up.  Unfortunately the optimizer is not smart enough to fully know what is happening on the linked server and you will likely need to dig deeper into why performance isn’t what it should be.

Do work on one side of the fence or the other

The general rule of thumb I follow with linked server queries is to do as much work as possible on one or the other side of the linked connection.  Performing joins between local tables and linked server tables can lead to poor and unpredictable performance.

In many cases I’ve seen where a local query joins to tables on a linked server and OLEDB waits pop up intermittently.  In these cases you should reconsider joining to the data on the remote server and perhaps load the data into a local #temp table instead.  From there you can join to the local #temp table.  This gives the optimizer much better row estimates.  This of course might not be possible if the amount of data from the linked server is large.

Another approach is to use staging tables where remote data is pulled to the local server.  This is more appropriate for ETL type processing (which is where I typically see joins to data from a linked server).

These might seems like extra steps that will prolong processing time but if you have significant OLEDB waits the tradeoff might improve overall performance.

Look in your plans for “remote” operators

Sometimes you might not realize you are interacting with a linked server because somebody created a view on the local server which references the linked server.  Look in your plans for any remote operators.  That will help you spot that you are involving a linked server.

Keep it simple

The simpler you keep queries that access linked servers the more likely performance will be predictable and faster.  Good luck with your tuning.