SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Blocking Monitoring


SQL Server Blocking Monitoring

Author
Message
BJ Hermsen
BJ Hermsen
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 880
Comments posted to this topic are about the item SQL Server Blocking Monitoring
ashish.mukherjee
ashish.mukherjee
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 46
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
philcart
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4114 Visits: 1436
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

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
ashish.mukherjee
ashish.mukherjee
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 46
Thanks a lot for you help Smile
mjswart
mjswart
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 232
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
BJ Hermsen
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 880
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
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32266 Visits: 18552
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

BJ Hermsen
BJ Hermsen
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 880
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 Smile
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32266 Visits: 18552
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 Smile


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

jswong05
jswong05
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 476
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
:-P
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search