pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Friday, February 18, 2011

"Heterogeneous queries and use of OLEDB providers are not supported in fiber mode."

This is a classic.


I've seen this in environments where someone is trying desperately to resolve SQL Server performance issues.  Out of the blue, linked server/DTC queries will begin to fail with the following error message:


Heterogeneous queries and use of OLEDB providers are not supported in fiber mode.

My colleagues and I have seen this more than once, and each time it is the result of someone seeing "lightweight pooling" in SSMS. Finding that it sounds agreeable (who doesn't want their pooling lightweight, am I right?), they turn it on and with the next SQL Server service restart, it starts breaking things.

Having not realized that the setting didn't solve their problem immediately, or even after the service restart, they forget the set it back.  Lightweight pooling/fiber mode rarely solves any problem, at best it gives a slight boost to the performance of extremely high workload SQL Servers.  It doesn't affect any problems dealing with horribly inefficient code (Entity Framework), and as we have seen more than once, it actually makes things much worse.

That's where blogs like this one can come in handy.

If you're getting this error message, go to SSMS and uncheck "Lightweight pooling", and find some (additional) downtime to restart the SQL Server service.  The error message will go away.  

If your environment really does need fiber mode turned on, you've already tried a lot of other advanced methods and settings and hardware.


No comments:

Post a Comment

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.