January 19, 2012 at 4:58 pm
Hi everyone,
I was told by someone that they have two servers that are exactly the same in every regard, and two databases on those servers that are also exactly the same. The problem that he was having was when he ran a query from one server to another (using linked server), the query would run 10 times slower than vice versa. How can that be? I thought maybe the execution plans were different, but he said that they were not. I am thinking that maybe the network throughput and the network card/driver should be checked. Any other ideas?
Also, as an interesting side-question, under what conditions can the query plan for the same query become different on these two servers? I was thinking that if statistics on a column/index on the two servers were created on different dates and the data had changed in that time, then there is a chance that the statistics are wildly different from each other and so the query plans would be different also. Can you think of any other scenarios when this could happen?
January 19, 2012 at 5:10 pm
shahgols (1/19/2012)
Hi everyone,I was told by someone that they have two servers that are exactly the same in every regard, and two databases on those servers that are also exactly the same. The problem that he was having was when he ran a query from one server to another (using linked server), the query would run 10 times slower than vice versa. How can that be? I thought maybe the execution plans were different, but he said that they were not. I am thinking that maybe the network throughput and the network card/driver should be checked. Any other ideas?
The linked server query is probably not being run remotely for whatever components it can. Without seeing the query directly, it's hard to say for certain, but the most likely culprit is the data volume being pushed through the link. Maybe I'm just not understanding exactly what you meant though.
Also, as an interesting side-question, under what conditions can the query plan for the same query become different on these two servers? I was thinking that if statistics on a column/index on the two servers were created on different dates and the data had changed in that time, then there is a chance that the statistics are wildly different from each other and so the query plans would be different also. Can you think of any other scenarios when this could happen?
Statistics is a good culprit to check. Different Service Packs and the like are also possible. What parameter the query optimized for is another possibility (look into parameter sniffing). There are a number of possible culprits, but you're correct, I'd start with statistics.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2012 at 5:29 pm
Thanks Kraig for the reply.
The linked server query is probably not being run remotely for whatever components it can.
Not sure what this means, can you please explain further or tell me what to google for so that I can read up on it?
Without seeing the query directly, it's hard to say for certain, but the most likely culprit is the data volume being pushed through the link.
It's the same amount of data. There are two servers, same hardware, same configuration, same SQL Server release, same database, same everything. When you run a query on server A that reads from server B, it runs, lets say in 5 seconds. When you run the same query on server B that points to server A, it runs in 50 seconds. I was told that the execution plans are the same too. How would you go about troubleshooting this?
January 20, 2012 at 5:49 am
Frequently with a linked server what happens is that ALL the data in referenced tables is brought across the wire and then filtered on the local server. If you use OPENQUERY to make the queries across a link you can put the processing onto the remote server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply