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.