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

Best way to Monitor for blocks every 2s Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 12:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:22 AM
Points: 100, Visits: 506
We have process that has a 4 second SLA for the round trip to and from the Web Services server.
I am trying to identify any blocks that are > 2 seconds.

I am unsure of the best way to do this considering the small threshold.

I have altered the [blocked process threshold (s)] to 2 s.

I can use the SQL Profiler Blocked Process report as an ad-hoc solution.

I don't think I can use my existing blocking monitor script run as job as every 2s is a bit much for the job to run (as opposed to the 5 minute intervals it currently runs).

So I was thinking of setting up the Blocked Process as a Server Side Trace (to a table)? (though I am sure I have read somewhere that the SQL Profiler Blocked Process is not as accurate as using a DMV solution i.e it can miss some blocks???)

Have to have a think about the best way to ensure it runs all the time though, and restarts when the server restarts.

I don't think an Alert can be written to a table without calling a script / job every time is there?

But of course their may be a better or simpler solution that is used to identify this type of blocking.

thanks for any ideas.
Post #1407428
Posted Wednesday, January 16, 2013 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 3, Visits: 124
How about...

1. create a table with the columns based on your query.
2. create sql statement so it inserts into the table from your query but include a loop to run the query x number of times
3. set it up as a job to run e.g. every 10 minutes
4. and don't forget to create a data purge on the table

You can then query the table or design a report to pull up any blocks over time.
Post #1407822
Posted Wednesday, January 16, 2013 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 3, Visits: 124
Rae-Jin Shui (1/16/2013)
How about...

1. create a table with the columns based on your query.
2. create sql statement so it inserts into the table from your query but include a loop to run the query x number of times
3. set it up as a job to run e.g. every 10 minutes
4. and don't forget to create a data purge on the table

You can then query the table or design a report to pull up any blocks over time.


That is the query within the script you use to monitor blocking.
Post #1407831
Posted Wednesday, January 16, 2013 9:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 320, Visits: 964
And remember to include host, IP, process id, login, and SQL text of BOTH the blocker and the blocked.
make sure you store the wait time in ms so you can determine the top of the block chain (if one is occurring).

Report should be sorted by wait time desc.
Post #1407941
Posted Wednesday, January 16, 2013 9:51 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
Best Way is to create a server side TRACE with the Blocking events and monitor the Trace with a TSQL using ::fn_trace_gettable Function from a SQL JOB.
OR Write the Blocking/DEADLOCK to SQL Error Log (Be very cautious - Log file size might Grow faster) and create a TSQL-Job to Monitor the SQL Log.
OR a solution here: http://www.sqlservergeeks.com/blogs/Abhay_c/sql-server-bi/472/sql-server-real-time-monitoring-using-wmi-classes-part-1
OR use the Activity Monitor to monitor blocking Actively (but hiccups with continually keeping the monitor up)


Maninder
www.dbanation.com
Post #1407953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse