Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Jun 2008
    Posts
    17

    Unanswered: Sporadic timeout expired error in DB updates

    Hello all,

    Our website has been running for years, and we haven't had code changes in months. A few weeks ago we moved our DB from SQL Server 2000 to a new SQL 2005 server, and now we're starting to get this on updates/inserts/deletes error 3-4 times a day:

    Code:
    Timeout expired. The timeout period elapsed prior to 
    completion of the operation or the server is not responding
    Once it happens for the first time, it happens with any query that updates/inserts/deletes records. How can the same code work most times and then other times cause this error? Regular select queries don't cause this error.

    The problem goes away by itself eventually, or it goes away after we restart the MSSQL service.

    Any help would be GREATLY appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    run sp_who2 and check for blocking.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2008
    Posts
    17
    Ok, I ran it but I'm not sure how to read the results... how do I recognize blocking? what should I look for?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    when your time outs are happening, there will be a SPID listed in the BLKBY field.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2008
    Posts
    17
    I'm sorry I'm not really familiar with this subject. Let's say I find a SPId (does that stand for system process?) that is blocking (will it actually say blocking in the status?), what do I do with it?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    hire a consultant.

    take that spid and run it through DBCC INPUTBUFFER to see the SQL causing the problem.

    what you are looking for is the SPID number in the BLKBY column.

    this is just the first thing to check.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    sounds like index statistics out of date to me.

    sp_updatestats in database in question, especially if you went from 2000 -> 2005.

  8. #8
    Join Date
    Jun 2008
    Posts
    17
    Quote Originally Posted by Thrasymachus
    take that spid and run it through DBCC INPUTBUFFER to see the SQL causing the problem.
    what you are looking for is the SPID number in the BLKBY column.
    this is just the first thing to check.
    Ok I looked at the SPids that are causing the blocking during my timeouts.
    It appears that one of the update queries blocks other updates (same query that update hits).
    I created a table and used INPUTBUFFER to inset the blocking SPids into it.
    These are the results...

    Language Event (@ArticleID int)update Hits set Hits=Hits+1 where ArticleID=@ArticleID
    RPC Event sys.sp_executesql;1
    Language Event (@RotatorID nvarchar(4000))SELECT [RotatorAds].[Tag], [RotatorAds].[Priority] FROM [RotatorAds] WHERE ([RotatorAds].[RotatorID] = @RotatorID)
    Language Event (@ArticleID int)update Hits set Hits=Hits+1 where ArticleID=@ArticleID
    RPC Event sys.sp_executesql;1
    Language Event (@ArticleID int)update Hits set Hits=Hits+1 where ArticleID=@ArticleID
    Language Event (@ArticleID int)update Hits set Hits=Hits+1 where ArticleID=@ArticleID
    RPC Event sys.sp_executesql;1

    Maybe I'm doing something wrong in my application code?
    This is my code:
    Code:
    string queryString = "update Hits set Hits=Hits+1 where ArticleID=@ArticleID ";
    SqlCommand dbCommand = new SqlCommand();
    dbCommand.CommandText = queryString;
    dbCommand.Connection = dbConnection;
        
    IDataParameter dbParam_ArticleID = new SqlParameter();
    dbParam_ArticleID.ParameterName = "@ArticleID";
    dbParam_ArticleID.Value = ArticleID;
    dbParam_ArticleID.DbType = System.Data.DbType.Int32;
    dbCommand.Parameters.Add(dbParam_ArticleID);
        
    int rowsAffected = 0;
    dbConnection.Open();
    try {
               rowsAffected = dbCommand.ExecuteNonQuery();
         }
    finally {
                   dbConnection.Dispose();
             }
    dbConnection.Dispose();

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    How long do these queries take to execute....

    update Hits set Hits=Hits+1 where ArticleID=@ArticleID

    SELECT [RotatorAds].[Tag], [RotatorAds].[Priority] FROM [RotatorAds] WHERE ([RotatorAds].[RotatorID] = @RotatorID

    Is there an index on Hits.ArticleID? You can check by running sp_helpindex Hits. If not, you might want to consider one. If there is you may want to check it's fragmentation, by running DBCC SHOWCONTIG on that index.

    Same thing for RotatorAds.[RotatorID].

    And yes you may want to update your statistics if you did not after the upgrade.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am no C# expert but that looks OK, but you may want to consider using stored procedures.
    Last edited by Thrasymachus; 06-29-08 at 13:24.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Jun 2008
    Posts
    17
    The queries are very fast, they take practicly no time to execute usually, there are only 50 rows in the rotatorAds table and yes there's an index on RotatorID.
    There is also an index on Hits.ArticleId (there are 11,500 rows there). I ran a trace with SQLProfiler, and most of the time the update hits query takes 5-10 (duration) but sometimes it suddenly jumps to 30,500 duration. I don't understand what could cause it..

    The index on hits.ArticleId has 95% page fullness and 58% total fragmentation, I looked in the properties.. should I rebuild it? Also I noticed in the properties that "use row locks" and "use page locks" when accessing the index are turned on. Could that be causing the problem? This is a small table, only 3 columns, I think the page locks might be killing me here.

    and yeah I updated all statistics.

    Oh and why do you think using stored procedures is better than what my code does?
    Last edited by lior32; 06-28-08 at 16:49.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are these the spids doing the blocking or the spids being blocked? you should be concerned with the SPIDs listed in the BLKBY column.

    you could add the WITH (NOLOCK) query hint to the select.

    for the index that is 58% fragmented, I would rebuild or reindex.

    The argument for stored procs is a little weakened in 2005 because the execution plans are cached at the statement level where they used to be caced at the procedure level. But there are many reasons why you should not query the tables directly and the biggest one for me is security in 2005. It is also generally easier to deploy a change to a procedure than a change to an application if something changes in your code.

    Also, have you looked at the execution plan for the update statement?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Jun 2008
    Posts
    17
    Yes, those are the spids doing the blocking.

    If I add the NOLOCK hint, what's gonna happen when 2 update queries reach the server at the same time?

    I have looked at the execution plan for the update, it seems fine to me, index seek.

    I removed the "use page locks" from the index a few hours ago but now I still got the same error again
    Last edited by lior32; 06-28-08 at 22:14.

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I do not think you can use nolock with an update.

    as for the spids that are getting blocked, if they are selects, I might consider using the NOLOCK hint IF you can live with the possibility of dirty reads.

    OK, So we have an update statement that occasionally takes longer than 30 seconds and it blocks all other locks while it is doing it's business. The update uses an index seek, and the fragmentation and the statistics are fine. At this point I think we need to look at hardware and possibly changing the way the database is designed with regards to the Hits table.

    While your timeouts are happening, have you taken a look at your AVG DISK que length in the performance monitor?

    How big is HINTS in terms of rows? If it is huge, have we considered partitioning or archiving?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Jun 2008
    Posts
    17
    The SPids that are getting blocked are updates to any other table. Any update statement that I try to run while I'm getting the timeout fails, while selects succeed. Meaning, if I'm trying to update the Articles table (different one from the Hits table) I get a timeout, but a select from Articles works fine.

    About hardware, I really find it hard to believe it could be causing this since I'm running on a dedicated server, a new one. Maybe it has something to do with the properties of the datafile which the database is using?
    I haven't looked at the AVG Disk que lenght (you mean in the task manager right?), I will next time.

    How big is HINTS in terms of rows? If it is huge, have we considered partitioning or archiving?
    Not sure what you mean here, I don't use HINTS at all in my code. I don't see any reason for partitioning.

Posting Permissions

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