SQL Server Blocking Monitoring

  • BJ Hermsen

    Hall of Fame

    Points: 3123

    Comments posted to this topic are about the item SQL Server Blocking Monitoring

  • ashish.mukherjee

    Valued Member

    Points: 64

    Hi

    Nice Article,i am a newbie so just want to know if i implement the solution suggested by you to monitor the blocking issue in production server will it involve any overhead.

    Thanks

  • philcart

    SSC-Forever

    Points: 47794

    I'd suggest never run the Profiler GUI on a production server without very limited events/columns and strict filters. Even then, on a very busy server, there will be an adverse impact on the server.

    Better method is to use a server side trace created via the sp_trace_create. Have the output of the trace go to a file then open the file in Profiler once the trace is closed.

    For further info see,

    http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

    http://msdn.microsoft.com/en-us/library/ms191443(v=SQL.105).aspx

    --------------------
    Colt 45 - the original point and click interface

  • ashish.mukherjee

    Valued Member

    Points: 64

    Thanks a lot for you help 🙂

  • mjswart

    SSC Enthusiast

    Points: 168

    philcart (5/19/2011)


    I'd suggest never run the Profiler GUI on a production server without very limited events/columns and strict filters. Even then, on a very busy server, there will be an adverse impact on the server.

    Better method is to use a server side trace created via the sp_trace_create. Have the output of the trace go to a file then open the file in Profiler once the trace is closed.

    BJ (the author) includes a script that creates a server side trace. But even so, the trace as BJ defined it is very limited in terms of events and columns. And the event is very lightweight. (It was designed to be that way). As BJ defined it, I would feel comfortable creating that trace in order to capture blocking problems in production.

    Also let me plug a tool I wrote to help analysis of the blocked process reports after collecting them:

    http://sqlblockedprocesses.codeplex.com/

    I've found it useful.

  • BJ Hermsen

    Hall of Fame

    Points: 3123

    As with any monitoring processes we can often gather so much data we find it hard to identify issues or trends. This was intended to be very pointed information.

    Also I would almost never recommend any constant running trace other than the built in system traces of SQL Server 2005 and up. There is overhead involved. Are there situations where you can afford a trace to monitor things all the time? Sure but the canned response would be to do periodic pointed monitoring to proactively catch issues.

    As a side note, remember that all high IO OLTP databases will encounter a certain amount of blocking. This is not a bad thing. This is there to protect your data consistency. It is a matter of finding a balance in how the code is written to make the blocking minimally impacting. SQL Server does a great job of finding that balance for you however the code still needs to be written with skill.

  • SQLRNNR

    SSC Guru

    Points: 281252

    Here is a more updated query for the first one listed in this article

    select er.session_id, blocking_session_id, wait_type

    , wait_time, last_wait_type, database_id

    , user_id, er.cpu_time, er.reads + er.writes AS Physical_IO, granted_query_memory

    , es.login_time, es.last_request_start_time, es.host_name

    , program_name, nt_domain, nt_user_name, login_name

    from sys.dm_exec_requests ER

    INNER JOIN sys.dm_exec_sessions ES

    ON ER.session_id = ES.session_id

    where blocking_session_id <> 0

    or ER.session_id in (select blocking_session_id from sys.dm_exec_requests)

    Thanks for the article.

    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

  • BJ Hermsen

    Hall of Fame

    Points: 3123

    Thanks Jason. Self admittedly I have not taken the time to migrate to all the new system objects as I am still in a very mixed world 🙂

  • SQLRNNR

    SSC Guru

    Points: 281252

    Robert Hermsen (5/20/2011)


    Thanks Jason. Self admittedly I have not taken the time to migrate to all the new system objects as I am still in a very mixed world 🙂

    The sysprocesses view is probably one of the last that should be migrated (scripts referencing that view). There are some shortcomings with the new DMVs that weren't addressed in 2005,2008, and R2.

    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

  • jswong05

    Hall of Fame

    Points: 3503

    Finding blocking is one thing, I have done a monitor in web .Net since 2005. See my Houston PASS 2008 presentation.

    Resolving root cause of blocking is another topic, see my PASS SQL Saturday #57 2011 presentation. What should you do when you see more blocking than your system can handle?

    Regards,

    Jason
    http://dbace.us
    😛

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

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