SQL Server break down
-
Hello,
my colleque and me overtook 3 a tier project. The basic problem that we have is, that with MSDE (local) our application works fine but over the LAN (TCP/IP) we have a problem. The server stops acceptig any login after some minutes (down to half a minute) of work after about 10 up to 30 seconds it resumes work. The trouble I can´t work around, is that the middle tier is in VB and as far as I know it can´t keep any information in mind. So that I can´t keep the connections alive and due to this the server is making a login and a lockout for each SQL statement. As far as I can see the server is "closing the door" as soon as it is to mutch for it. We have already tested a lot: SQL Server on W2KServer High performant switches and so on.
As soon as I reduce the amount of SQL server statements which is indeed very high, the server starts to work fine.
Does anybody know how to keep connections in the middle tier in VB so that it does not need to login/logout each time? Is it true that the SQL server 2000 stops accepting logins if it is too much under pressure?
thanks in advance
HMB
-
MSDE can only accept 5 concurrent connection at any point of time.
-
Correction, Limit to eight active connection (not Five) at the same time.
-
Hello again,
that´s not the problem I think. It also happens with normal SQL Server and if i Monitor with Enterprise Manager there is only one or two (for each middle layer process) connection to the client´s process.
-
Problem seems to be solved.
it looked like that the server was so much under presure, that he could not answer anything.
After eleminating the bottleneck in :
Select TOP 1 WeldID from Welds WITH (NOLOCK)
Where Welder = 'TEST101' Order By TImeW
OPTION (FAST 1),
where
"OPTION (FAST 1)" was not good
the perfomrance is ok and the server can answer now much more faster.
the strange thing was (is) that this bottleneck was not such bad when Server and Client are on one machine than if they are on two machines and connected via ethernet (even gigabit). ( the sql statement was a part of a stored proc and all fields were indexed )
HMB
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply