Post reply

What's blocking

  • April 22, 2008 at 10:23 am

    #123453

    Hi,

    I have script that I use to monitor blcked/blocking SPIDS. I am trying to tie this information to actual blocked resources.

    We have extensive blocking going on the database because of the application that queues the requests to the database and apprently create blockings. We have over 60000 tables in the database which makes it practically impossible to use lot of GUI tools.

    Blocked processes moved pretty fast too. By the time I get SPID info and try to see locks, SPID is gone.

    Does anyone have any script that ties the info for blocking/blocked SPIDS to the lock it is waiting on?

    or any suggestion on how to get some useful info from monitoring processes using sp-who2 or other scripts to monitor blocking?

    Thanks,

  • April 22, 2008 at 12:59 pm

    #805699

    http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/07/16/more-tom-davidson-magic.aspx

    the docs are also on my website http://www.grumpyolddba.co.uk

    I'm sure sql central also has loads of scripts for sorting out blocking as it's a fairly common issue

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • April 23, 2008 at 3:10 pm

    #806317

    Thanks Colin

    I ran waitstats on my db.

    This is top 5 lines of what I got after running it for 30 minutes:

    wait type wait time percentage

    -------------------------------------------------------------------------------- -----------------

    ***total*** 1631839489.0 100.0

    OLEDB 1617436288.0 99.1

    LCK_M_X 6794000.0 .4

    PAGEIOLATCH_SH 4236380.0 .3

    LCK_M_U 1130120.0 .1

  • April 23, 2008 at 3:47 pm

    #806324

    Oops

    Topic was posted before I finished typing.

    We have this silent fight and blame game going on between dbas and developers. Basically the application queues up tons of application calls to db using the handful app tables. they lock the table, process the SPID-app call and release the lock. Again locks the tables for the subsequent call.

    Anything DBAs say have to be supported with proofs. So, i am trying to prove taht majority of the performance bottlenecks come from the poor appliction.

    I have added a statement to capture sysprocesses while capturing waitstats to see the application info.

    When OLEDB is 99% of the wait time what can I make out of it? what else should I monitor besides disk activities? Any suggestion?

    I am still trying to fin the script that would tell me the table name where the locks are occuring along with the blocking SPID info. Script I wrote doesn't work properly. By the time it finds the info, SPID is gone, so it is unable to capture the lock info.

    Any suggestion would be helpful.

    Thanks

  • April 23, 2008 at 3:55 pm

    #806328

    Start looking at the statements that cause blocking. This is a handy script I've used in some ocassions -since tracing was not an option.

    SET NOCOUNT OFF

    DECLARE @blk_spid as int

    DECLARE @blk_sqlhandle as binary(20)

    DECLARE @blkQuery as varchar(8000)

    DECLARE blk_Cursor CURSOR

    FOR SELECT spid,sql_handle FROM sysprocesses WITH (nolock) WHERE blocked > 0

    OPEN blk_Cursor

    FETCH NEXT FROM blk_Cursor

    INTO @blk_spid,@blk_sqlhandle

    WHILE @@fetch_status=0

    BEGIN

    SELECT @blkQuery = text FROM ::fn_get_sql(@blk_sqlhandle)

    PRINT 'blk_spid: '+cast(@blk_spid AS varchar(50))+' - QUERY: '+@blkQuery

    FETCH NEXT FROM blk_Cursor

    END

    CLOSE blk_Cursor

    DEALLOCATE blk_Cursor

    GO

    The script as is, will basically scan through all SPIDS, find those who are being blocked, and output the actual statement buffer -you should modify it to retrieve the SPID of the blocking process instead.

    Many neat applications can be derived from this. Good luck!

  • April 23, 2008 at 5:02 pm

    #806342

    Here's something to get you started:

    Select S.session_id, S.host_name, S.program_Name, S.Login_name, R.request_id

    , L.*

    , r.start_time, r.status, r.Command, r.database_id, R.blocking_session_id, R.wait_type, R.wait_time

    From sys.dm_tran_locks L

    Join sys.dm_exec_sessions S on L.request_session_id = S.session_id

    Left Join sys.dm_exec_requests R on S.session_id = R.session_id

    And L.request_request_id = R.request_id

    Where L.request_status!='Grant'

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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