June 30, 2003 at 10:28 am
First, our set-up:
We manage a web site consisting of two Sql Server boxes, and three web servers. Database 1 has mostly data-driven web content; database 2 has mostly user profile information. Database 2 is not directly accessed by our web site code - all queries for it hit stored procedures on db1, which then call db2.
Here's what happened:
Last week, our client (who we manage the servers for) launched a promotion which encouraged a large number of new users to create profiles. The web site got very slow (not unsurprisingly). The second database server was very busy; the first server showed almost no activity whatsoever (also no big shock). However, we were getting connection errors when connecting to db1.
This brings up a couple of questions in my mind:
1) How are connections handled between linked servers? Could the connection error have been one between db1 and db2 over the link?
2) Books Online indicates that there is a maximum number of connections per client; is there a similar setting on the server side?
R David Francis
July 3, 2003 at 8:00 am
This was removed by the editor as SPAM
July 3, 2003 at 12:04 pm
You didn't happen to mention all of the impact in the procedures from db1 to db2. We had very slow response accessing a second machine using the machine.database.dbo.table method - especially updating or performing transactions. For us, the answer was to always access the second machine through stored procedures and performance immediately became excellent.
Guarddata-
July 3, 2003 at 12:24 pm
Actually, up until two months ago, all this data was on one server. We split user profile information off to another server to lighten the load and speed things up, and it worked.
However, we did not directly access the tables on database2 from database1; instead, we wrote stub procedure on db1 that simply called the identically named procedures on db2; db2 did all the actual table work.
R David Francis
July 3, 2003 at 12:31 pm
Have you reviewed the execution plan on the procedure in database2? Sounds like you might have a performance issue.
It wouldn't hurt to verify the setup of your connection limit. We found one of our servers that had the default 10 active functions even though our license different. Obviously we had failure every time the server got busy.
Guarddata-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply