Load problem using linked server

[vaidyanathanpc]

Hi,
We have an up and running web application. Yesterday we added two more systems to our application. The two systems are connected to our SQl Server through WAN using sp_addlinked server. Now the performance of our system has gone down like anything. When we removed the linked server the performance was good. When the linked servers were established, the performance is going down. Does linking servers from remote locations cause perfromance issues. The two servers at other locations have only select privileges on our database.

Thanks in advance
Regards,
P.C. Vaidyanathan

 

[moonshadow]

I don't know the answer to your question, but I may have a solution to your problem. Instead of using Linked Servers, why not put a copy of your main database on the other two servers. You could either do this using replication, if you need to keep the data right up to date, or you could backup the main database each night, copy the backup to the secondary servers, and restore it onto them. The secondary servers would then have copies of the database from the night before. As the two secondary servers only read the data (just have select priviledges), one of these solutions would be ideal, and easy to implement.

 

[vaidyanathanpc]

Hi,
We have about 45 systems at different locations talking to our database. So we have all the systems talking to our database thru linked server. By the way, I think the problem is not due to linked servers. The CPU time taken by SqlServer is increasing like anything even when we have dropped all the linked servers. Any idea what could be the problem?? SQL server is taking up most of the RAM and our application is hanging.

Thanks
P.C. Vaidyanathan

 

[moonshadow]

If you're SURE it's SQL Server taking the processor time, then it's probably a dodgy query. Run SQL Profiler and pick a trace for slow queries. tlbroadbent has recently answered another post on troubleshooting performance problems. When we had this, it was a stored procedure scanning a large table 3 times, taking ages to run and locking up the server.

 

[vaidyanathanpc]

Hi,
I did check out in profiler. Did not get to see anything which was locked or consuming CPU. How do I know from the profiler that a process is eating up CPU?

Thanks
P.C. Vaidyanathan

 

[tlbroadbent]

I'd check on the duration, cpu and io time of SQL statments and proceures using SQL Profiler. Linking you server to another server should not hinder performance. However, the queries run from remote sites can hinder performance. Profiler can also show you the text of the queries being executed. If a query has a long duration or consumes a lot of CPU time, then analyze the query for effiency. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.