March 27, 2005 at 9:00 pm
I have a few definitions of linked servers and they had been working fine all the while. The linked servers are located at another site and are accessed via VPN (the inconsistency is already noted). A typical SQL across the network usually took about 3-5 sec to complete. However, it just starts from one fine day, the same SQL now takes more than 20sec to complete. As far as the database server is concerned, there were no changes made that could result to the slowness. One phenomemon noticed is the SQL server seems to take a longer time to carry out the handshaking, e.g. connection etc
Can someone help to suggest:
a) ways to improve SQL efficiency between linked servers
b) cause of problem and solution to it.
Thanks!
King
March 28, 2005 at 2:54 pm
There are many possibilities for the slowness. For example, (1) are there more users or applications running against the server ? you should run perfmon to check the activities on that server. (2) are there much more data now than before ? when you said no changes, do you mean hardware or data ? (3) do you see any errors in error log or event viewer ?
-Jimmy
March 28, 2005 at 7:06 pm
Thanks for your suggestions, Jimmy. I do ensure that the servers are in pink health, the CPU and memory are all fairly utilised. To address your questions: 1) The number of users and applications have remained the same throughout. There is no sign of server being overloaded. 2) The tables concerned only have record counts ranged from 5K to 10K, and the records are inserted in a slow incremental rate, e.g. < 10 records per day. There have been no changes to the hardware and database config. 3) There have been no errors found in those logs. Most importantly, the general databases performance is still intacted, only queries using 4-parts-naming ([linkedserver].[remote database].[owner].[remote table]) has a significant drop in performance. E.g. I have servers A and B, and server B is defined as a linked server at server A. From server A, if I connect to database B and perform a sql on a table, the return can be instantaneous; however if I connect to database A and run the same sql against database B using 4-parts-naming, the return will take quite some times to come back. I do understand the performance constraint of using linked server, but the recent drop in performance is too abrupt and significant. Lastly, I notice that the performance is usually slower if a join is between a local table and a remote table. Now I am experimenting with inner remote join, and I am getting the following error message "Could not enforce the remote join hint for this query." Any way to overcome this?
Thanks, King
March 29, 2005 at 9:37 am
Is it slow only when you do the join between local and remote server ? try not to join a remote table when it is too big. You could DTS the data you need from remote server to local, then do the join.
If the slowness only happens when you do the join, then it is possible the indexes are out of date, or it never had any indexes for the columns you are joining.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply