Post reply

Locking/Blocking issues

  • July 19, 2011 at 9:16 am

    #254673

    In SQL 2000 it was very easy using the Ent. Manager to see what is what was blocking and what was blocked by. I don't know why Microsoft did not have this in SQL 2008 as it was a very helpful tool.

    We have a new SQL 2008 app and we are experiencing locking/blocking issues and am having a very difficult time trying to tell what SQL Statement is the blocking one.

    I have used SP_WHO2 and a few other things but am still coming up empty.

    Has anyone come up with anything that they use? I am poking around to find a better way to detemine the statement causing the blocking.

  • July 19, 2011 at 9:26 am

    #1354902

    Try running a query trace or a block trace on that server.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 19, 2011 at 10:01 am

    #1354937

    Have you tried activity monitor in SSMS?

    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

  • July 19, 2011 at 10:30 am

    #1354981

    Yes I tried it. There is so much blocking/locking and so many connections I am having a difficult time decifering who is the blocker even using the drop down boxes to narrow down what is displayed. I have found some queries to run that might help. When it happens again I will try them.

    One of the problems is that this app uses one userid for everything so I cannot narrow down things specific to specific actions. There is no way to change this because it is all in the app which adds to the problem of not knowing who is doing what.

    I have been looking at all of the SQL statements that run and have added some indexes to help out the longer running statements to help throughput.

    Stats are updated on a regular basis and indexes got rebuilt last night as well. Not that that has much of an impact at all. Just thought I'd add that to this thread.

  • July 19, 2011 at 10:59 pm

    #1355339

    Hi

    Just remeber that blocking is a symptom , you can either find some code that is not optimal or that the server don't have resources.

    Also make sure that your indexing is done after hours if it's not done online.

    Also make sure you didn't fiddle with recovery interval or any manual checkpointing or that you have funny backup applications that freeze io.

    When blocking occurs you can grab the session id and looking in sys.dm_os_waiting_tasks.

    It will give you an indication of what is blocking what. It also give you the type, if it's contention on the page, an index and the reason.

    Cheers

    Jannie

  • July 20, 2011 at 6:27 am

    #1355523

    Thanks all for the help. The blocking/locking happened again late in the afternoon yesterday and I was able to pinpoint it down to ASYNC_NETWORK_IO. I found some good articles on it and are working through the issue.

  • July 20, 2011 at 11:38 am

    #1355902

    Two really useful tool for detecting blocking is sp_whoisactive and server side trace with blocked process report. Latter will allow you to actively monitor and log blocking info and that trace isn't too intrusive so I tend to let it run for a while to detect for blocks.

    -------------------------------------------------
    Will C,
    MCITP 2008 Database Admin, Developer

  • July 21, 2011 at 9:20 am

    #1356509

    I second the recommendation for using sp_whoisactive. See sqlblog.com for latest version and a 30-day blog series on how to effectively use the awesome free tool.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 21, 2011 at 9:25 am

    #1356517

    We got a user to come into my office as I had profiler running and Activity Monitor. We found as they used the app and navigated through this one specific screen that the call returned all of the rows but as they paged through each screen it would run another sql statement via a where clause to fill the screen and then we'd see the NETWORK IO Wait. It would end the next time they hit PF8 to get another screen full of rows and another where clause would return that data.

    They modified the program to close the cursor when done and use TOP in the SQL Statement to only return the data that is needed for the screen and that has fixed the issue.

    Thanks for everyone's feedback.

  • July 21, 2011 at 10:31 am

    #1356589

    Activity monitor, Script to find Head blocker- SQL Server 2005, 2008 and later[/url]

    Hope fully helps find the headblocker and you can point them who is blocking ...:-)

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • July 21, 2011 at 10:41 am

    #1356600

    Man that is great. Thanks for the link.

  • July 21, 2011 at 11:07 am

    #1356618

    Nope Noproblem.you are welcome

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • July 28, 2011 at 10:55 am

    #1360330

    I like the script. I think it would be a good idea to note on your blog how you created that script.;-)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 28, 2011 at 10:59 am

    #1360338

    SQLRNNR (7/28/2011)

    I like the script. I think it would be a good idea to note on your blog how you created that script.;-)

    Sure it might help us build a ton more in only a few seconds!

  • July 28, 2011 at 11:01 am

    #1360340

    Ninja's_RGR'us (7/28/2011)

    SQLRNNR (7/28/2011)

    I like the script. I think it would be a good idea to note on your blog how you created that script.;-)

    Sure it might help us build a ton more in only a few seconds!

    I think I just may do that 😉

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 20 total)

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