Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Blocking Monitoring Expand / Collapse
Author
Message
Posted Wednesday, May 18, 2011 9:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:42 AM
Points: 471, Visits: 842
Comments posted to this topic are about the item SQL Server Blocking Monitoring
Post #1111461
Posted Thursday, May 19, 2011 1:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:10 AM
Points: 2, 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
Post #1111494
Posted Thursday, May 19, 2011 3:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 2,693, Visits: 1,207
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
Post #1111545
Posted Thursday, May 19, 2011 3:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:10 AM
Points: 2, Visits: 46
Thanks a lot for you help :)
Post #1111547
Posted Thursday, May 19, 2011 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:23 AM
Points: 9, Visits: 206
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.
Post #1111608
Posted Thursday, May 19, 2011 6:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:42 AM
Points: 471, Visits: 842
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.
Post #1111630
Posted Friday, May 20, 2011 1:22 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:01 PM
Points: 17,694, Visits: 15,561
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1112690
Posted Friday, May 20, 2011 2:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:42 AM
Points: 471, Visits: 842
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 :)
Post #1112722
Posted Friday, May 20, 2011 2:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:01 PM
Points: 17,694, Visits: 15,561
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1112726
Posted Wednesday, June 8, 2011 8:14 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:38 PM
Points: 27, Visits: 473
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
Post #1122182
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse