January 17, 2012 at 6:33 am
Due to reasons beyond my control I have a DB on a RemoteServer that I only have low security on. To help with things I have my own server and have created a replica DB that runs transactions every hour to keep the DB the same as the RemoteServer DB. This is now working fine. All tables are matching.
My problem is that when I run a query on the RemoteServer it is quicker than when I run the same query Locally. I have the same number of Indexes as they were copied across when I copied the database structure.
I don't understand why a query would be slower when ran Locally(takes 3mins to run) than the RemoteServer(takes 45sec to Run).
I have used Database Engine tuning Advisor which has given me some addtional Indexes I can use, but this still hasn't made much in roads to the same query times as on the RemoteServer.
How can I get around this?
January 17, 2012 at 10:01 pm
There are many possible reasons that it's faster on the other server such as:
- storage performance (be it DAS or SAN); you could be on faster disk on the other machine
- memory; the remote machine could have a lot more memory meaning that the data you need to query is being held in cache ready for retreival
- server workload; other queries and actions can be running on the local machine taking up resources that would otherwise execute your query at the same speed
- CPUs; you could have significantly different CPUs in the machines. Faster CPUs leading to faster query returns
- Outdated statistics; you local statistics could be outdated but current on the remote machine.
The best thing you can do is get a comparison of the hardware. Also have someone provide you with an execution plan from the remote machine and compare that between the two SQL instances to see if they match.
January 17, 2012 at 11:07 pm
yes cain is described all the possibilities of it ...u need to check it one by one
Regards,
Shivrudra W
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply