Timeouts - where do I start?

  • csales

    Ten Centuries

    Points: 1219

    I've got some Siebel consultants busy importing data into a new sql box. They've been complaining about inconsistent performance and timeouts. My initial reaction was that it was there software.. however I've seen the problem and its definitely on the sql server side. Heres are some details:

    - SQL Box: Compaq ML370, 1.2 GB ram, 1 ghz processor

    - siebel database: 500 mb

    The statement that I've just seen a timeout on was the following:

    UPDATE dMmarriotUTtakeonTXN SET Status = 4 WHERE (Status = 0 or Status is null) AND ID = 44068

    This table has about 50k records and it has an index on ID. The update also only acts on 1 record.

    Looking at their log the process flies through - 4 or 5 transactions a second until suddenly a timeout. I cannot find any similarities between timeouts. There is nothing else running on this box, log backups etc are not running, theres plenty of disk space allocated, nothing funny happening on the network, no error message in the logs.

    Any ideas on where I even start trying to look at this?

    thanks

    Craig

  • jG

    SSCrazy

    Points: 2505

    Have you tried tracing the transaction through SQL Profiler?

    -JG


    -JG

  • Antares686

    SSC Guru

    Points: 125444

    Also what version of SQL are you using?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • csales

    Ten Centuries

    Points: 1219

    We're running 2000 SP2. The client (VB app) is using MDAC 2.7.

    I have not yet tried doing a trace. I dont think this will reveal anything as I doubt its not a sql query type problem but something else.. but am I willing to try it.

  • jG

    SSCrazy

    Points: 2505

    You should be able to capture enough info to show you where the timeout is occuring, if the transaction process and it is not timeing out in on the SQL Side the profile will help you rule out SQL Server.

    -JG


    -JG

  • well0549

    SSCrazy

    Points: 2259

    I had similar problems. I foud out that the clusterd index was broken in some way..... Try thge following. Deleted all indexes from the table and recreate them. It worked for me.

  • Justin-14543

    SSCrazy

    Points: 2017

    Check SQL Client Network utility and change default network library. That should do it.

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply