May 10, 2010 at 1:10 pm
Hi,
We have MOSS 2007 databases in SQL Server 2005. Sometimes, blocking is occuring and able to capture the queries blocking and blocked from monitoring tool.
Is there way to write them to error log as we write dead lock graphs to error log by enableing -T1222? Is blocking daily one or two times normal? what should we do after capturing the queries involved in blocking?
Thanks
May 10, 2010 at 1:28 pm
Q: what should we do after capturing the queries involved in blocking?
A: Review all application queries making certain all table selects have NOLOCK hints and all updates or inserts have (ROWLOCK) table hints. I have been DBA and developer of several VLDB projects and the ones that use the correct table hints for resolving this issue in all SQL statements never show blocking. Never. Make sure you do not try to resolve this by using SET statements. SET statements work well in Ad Hoc SQL statements, but not in SSIS package statements or any other compiled SQL code. They also are a good way of causing the issue you are trying to prevent.
May 12, 2010 at 4:23 am
Hi,
You can capture blocking queries / sql statement and it's related infirmations ( like CPU usage, I/O , read-write memory etc. ) and send it to your email id. Basically you have to set one SQL job for same which will run every 5 min / 10 min depends upon your requirement. This job will monitor the sysprocess and capture the blocking of sql ( use DBCC Inputbuffer or :fn_get_sql() ).I suggest you to save this captured data into one intermediate monitoring database and then filter these blocking data to send to your email id.
I have done this task few months back to monitor our SQL environment. I suggest you to monitor only those servers which having performance issue.
May 13, 2010 at 5:52 pm
jparker2 (5/10/2010)
Make sure you do not try to resolve this by using SET statements. SET statements work well in Ad Hoc SQL statements, but not in SSIS package statements or any other compiled SQL code. They also are a good way of causing the issue you are trying to prevent.
Can you explain this more? I have not heard of this issue before about using SET statements... So Stored Procedures using SET statements can cause blocking?
May 13, 2010 at 9:56 pm
Ajitgadge,
Hi,
You can capture blocking queries / sql statement and it's related infirmations ( like CPU usage, I/O , read-write memory etc. ) and send it to your email id. Basically you have to set one SQL job for same which will run every 5 min / 10 min depends upon your requirement. This job will monitor the sysprocess and capture the blocking of sql ( use DBCC Inputbuffer or :fn_get_sql() ).I suggest you to save this captured data into one intermediate monitoring database and then filter these blocking data to send to your email id.
I have done this task few months back to monitor our SQL environment. I suggest you to monitor only those servers which having performance issue.
Could you please explain me step by step process with scripts to implement this monitoring to capture blocking?
thank you very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply