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


Best way to Monitor for blocks every 2s


Best way to Monitor for blocks every 2s

Author
Message
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 733
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.
Raging Shui
Raging Shui
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 136
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.
Raging Shui
Raging Shui
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 136
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.
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 1019
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.
MannySingh
MannySingh
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1169 Visits: 787
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
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