Is your SQL Server Linked Server Query running slow? Check your permissions. Maybe this is old news to you but it wasn’t for me –>
It was a good day. We had finally migrated onto brand new hardware (and saw a 250% improvement in run times of most warehouse jobs). I had finally cleaned up the “interesting” security I had inherited (Linked Servers using SQL authenticated accounts with every Fixed Server Role and DB role granted, including SA overriding all the other attempts at access). Initial testing looked great. I couldn’t axe the Linked Servers but I fixed the permission to give them read on just the necessary objects, Least Privilege, Baby!
But then again… This is my blog and I don’t post too much about success stories now, do I? It hit, the next day I got the call –
“Hey Mike, the new SQL Server is really slow”
No way. Everything was running so much faster it was pathetic that it ever ran so slow. I debated in my head and even briefly on the phone but said I’ll take a look. Sure Enough! The plan was odd for their linked server query. On the older server the estimates were dead on. On the newer server (Same version of SQL, same everything, just older hardware and wide open permissions) the estimates were off and a sub optimal plan was selected. Really Suboptimal. Scans where I expected seeks, a seek where I expected a scan (on a large, for that database, table), an “interesting” join order chosen. I was banging my head on my desk contemplating what else could have changed when I gave the wide open permissions a quick shot to test. Sure enough, quick query, good plan. Did I mention I hate Linked Servers?
So I knew something was off with statistics somehow and I had realized that I was incorrect in assuming (I forgot to follow my own advice there) that statistics wouldn’t even come into play since it is a linked server query. Nope.. It was a Linked Server query between two SQL Servers and statistics do come into play. Distribution Statistics. Apparently, they can come into play with any OLE DB provider that takes advantage of them.
Alright. So Statistics Were At Play – But Why?
Well. Apparently, the (did I mention that I hate Linked Servers?) database engine will only provide those statistics if the calling user has the same permissions as required to run a DBCC SHOW_STATISTICS…. Ok, that sounds easy enough, I just have to look for that granular permission. Yeah. Right. Check out this excellent blog post by Linchi Shea to see what I found out when researching this issue. His post goes through the reason behind the issue and the permissions required to be able to see those statistics. Since his post helped me, I won’t give the final tidbit myself. Suffice it to say, however, that I had to grant more permissions that I wanted to fix the immediate issue.
Silver Linings – Because I try to be an optimist every so often, the good news from this is it at least gave me more ammo to try and convince folks to move away from Linked Servers. Only time and they will know how that pans out, though.
Mike,
You say you want to move away from linked servers…what do you want to move to? SSIS?
Thanks,
Robert
I guess I shouldn’t be so mean to Linked Servers without explaining. They aren’t truly evil incarnate, but they can be a bit of a pain for a few reasons:
1.) Security is often relaxed too much when most shops start playing with Linked Servers. Yes this can be fixed by proper processes and being tight with security but it doesn’t always work out that way.
2.) Performance -> I have often found Linked Servers used to bring over gobs of data and do cross server or cross platform joins. As you see in this post you do get statistics when working with a compliant source and you’ve opened the permissions up enough as Linchi discovered in his post. But you often don’t get statistics depending on the platform you are joining. SO I have seen some pretty horrible plans doing much more work than necessary with Linked Server queries.
3.) Weeds -> They spread and people rely on them too heavily.
4.) Wrong Tool For The Job –> A lot of times processes I see relying on Linked Servers could have been better served through SSIS, bulk loading, replication, etc. depending on what was trying to be accomplished.
All that being said, they still have their place and I don’t want the feature gone completely, just used more carefully and with things like those concerns in mind.
Really interesting post, thanks. This gives me another angle to try when troubleshooting linked-server query performance.