January 15, 2013 at 12:35 pm
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.
January 16, 2013 at 7:02 am
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.
January 16, 2013 at 7:26 am
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.
January 16, 2013 at 9:40 am
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.
January 16, 2013 at 9:51 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply