The company that I work for has an internet application that has been running in our client's environment for 2 years.
The SQL box is a quad Xeon 750 with 4 gig of ram and over 50gig of disk. This box has 4 databases that are used for the application. The primary database is about 1.5gig in size (.mdf file) the others are relatively small.
The box is queried from a separate windows 2000 server box with our COM based application running under IIS.
The boxes are located on the same switched LAN.
Up until recently the application has been running fine, but all of a sudden we have had masses of database time outs and the processor utilisation is always 80-100. In turn these time outs crash IIS eventually.
I Understand all of the ideas about increasing the timeout in a command object, etc.
I have monitored blocking on the SQL server and over a 30 minute period of live usage I saw 1 block and 0 deadlocks.
In addition to the above often our application gets an imediate time out error, hence after no time.
We have also monitored the network bandwidth between the two boxes and they only use about 30% on average of available bandwidth.
My 1st question is - Does SQL give a time out error (hence refuses connections) if it is running at 100% and cannot handle any more load??
My 2nd question is, In other peoples experience is the box we are using powerful enough.
My 3rd question is, Can the network have an effect on the time outs even though there is plenty of bandwidth.
Thank you in advance for your time in reading this and I appreciate any input that you may have.
Fastrack Software Ltd.
Thanks for your reply, it is appreciated. I thought the same thing about the box being unable to open any new connections, but I cannot seem to find any concrete documentation to support this, but at least I am not alone in this thought. :-)
Just for your information the box usually runs about 10-50 transactions per second and I would say about 75% read 25% writes.
I will have a look at using sp_who, I think identifying the spid and then the possibly the query will be very useful.
Unfortunately we do not have the application server utilisation figures for two months ago. I would imagine there has been an increase in usage, but previous to these problems the DB box usually ran at about 30-35%