SQL Server Blocking Monitoring

Question

SQL Server Blocking Monitoring

BJ Hermsen

Hall of Fame

Points: 3123
More actions
May 18, 2011 at 9:43 pm

#240623

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

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

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

ashish.mukherjee Valued Member Points: 64 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

ashish.mukherjee

Valued Member

Points: 64

May 19, 2011 at 3:50 am

#1326943

Thanks a lot for you help 🙂

mjswart SSC Enthusiast Points: 169 More actions · Answer 4

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 More actions · Answer 5

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: 281334 More actions · Answer 6

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 More actions · Answer 7

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: 281334 More actions · Answer 8

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: 3553 More actions · Answer 9

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
😛