August 12, 2014 at 9:43 am
Hi,
I was wondering if anyone could help me out with this one. I have a query (view) which resides on Server A (2005), it runs a query local and joins some tables across a linked server to Server B (2005). I'm getting very intermittent performance from the query, in most instances it takes a couple of seconds, in others it can take 30 minutes plus and basically causes an application timeout.
The query is static so parameters are not being fed in to it, the linked server account however wont have permissions to get the stats on the Server B tables due to not being a sysadmin, DDL admin or db_owner, I can understand that. The plan is also being purged from the cache after an hour or so, the view normally only runs once a day.
I'm confused as to why the performance is so hit and miss, I can understand if it's always rubbish (due to the missing stats) but the fact it's good most of the time confuses me as it's the same SQL statement every time.
Any ideas?
Thanks,
Nic
August 13, 2014 at 4:33 am
Is there any pattern as to when the query runs slowly? Could one of the servers be overloaded? Could some of the source tables be locked?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 13, 2014 at 10:57 am
There are so many variables in play. Server load, network load, locking and blocking that it is hard to be sure what is causing the problem.
Is the query against the view run at the same time every day?
What changes in the data on a daily basis?
Have you looked at wait stats for the query? Maybe collect the wait stats for the query when it is run every day for a week and compare the waits on good days, vs bad days.
As a rule of thumb I avoid joins across linked servers. I rather pull more data across the wire into a temp table and join on that, than have a cross server join. I've usually found this to perform better. Is there any way to do a filtered pull across the linked server and then join to the local table?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 14, 2014 at 3:26 am
Hi,
Thanks for the responses. Sadly there aren't any patterns, no signs of blocks, excessive server load etc.
I'm currently trying to go through the wait stats to figure out exactly what is happening, I'll let you know what I uncover.
Jack, I agree with you, cross server joins have in my experience been a source of problems, this is some legacy code that has popped up so I'm hoping that I get the chance to rewrite it and to make the whole thing more efficient, as it is now it's in a view which limits my options a little but I plan to change that.
Thanks,
Nic
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply