Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Doncaster UK
    Posts
    2

    Unanswered: Server Load and Timeout expiry

    Hi all,

    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.

    Simon Gratton
    Senior/Lead Developer
    Fastrack Software Ltd.

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Question 1:

    If the box is running at CPU 100% then it may not have the resources to open up and maintain new connections.

    If you have not changed the timeout on the server then it will be set to 0 which means no time out.

    Question 2:

    Your box sounds as though it is up to spec but would have to know what the transactional activity is on the box to say for sure.

    Question 3: Not sure , a dodgy network can have loads of issues

    What you need to do is

    a. Performance Monitor over a period of time using the processer couters and see what peaks your getting and when.

    b. Perform sp_who2 active at times of problems. See what spid is hogging the box. Find out what query is running on that spid.

    c. Has anyting changed on the server config or network in the last two months. Speak to you network admin team as well.

    d. Is the applications server taking on significantly more load than in the previous 2 months

    Give us a shout if you have any questions

  3. #3
    Join Date
    Feb 2004
    Location
    Doncaster UK
    Posts
    2
    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%

    Thanks again.

    Si

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •