Post reply

Database blocking-please help

  • September 7, 2010 at 9:13 am

    #157187

    Staff are unable to process orders, the application log file shows timeout expired:

    Microsoft OLE DB Provider for SQL Server-2147217871Timeout expired

    While staff are experiencing slowness or error messages, I run sp_who2 and I am able to see a few blocked processes. I'm not sure how to proceed, I looked into using NOLOCK but before I update all the stored procedures, is there anything that I am missing? I have reindexed all the tables and updated statistics but the blocking is still occurring. Please help!

    Thanks,

    Mon

  • September 7, 2010 at 9:28 am

    #1217393

    Before you use Nolock, check that the users are happy with possibly getting incorrect data....

    See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    Severe blocking is generally the result of poor indexing (not fragmented indexes, just not having indexes for the queries to use) and/or poorly written queries.

    Identify the queries that are causing and affected by blocking and look into optimising them. If it's urgent and you have no one in-house who can do query optimisation, consider getting a consultant in.

    If you want to have a go yourself, but don't know where to start...

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 7, 2010 at 10:12 am

    #1217417

    Thanks for the input. I'm confused though, if it's poorly designed or lack of proper indexes, why does it work perfectly for a few weeks then suddenly slows down.

    Thanks,

    Mon

  • September 7, 2010 at 11:00 am

    #1217458

    When you see the blocking with SP_WHO2, look to see which process is at the root cause. So, if SPID 17 is blocked by SPID 23, look to SPID 23. If 23 is blocked by 47, and 47 is blocked by 101, and 101 is not blocked, then that is most likely the place to start looking. run DBCC INPUTBUFFER(101) to see what it is doing. Perhaps you will see a stored procedure name to get started troubleshooting.

Viewing 4 posts - 1 through 3 (of 3 total)

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