Locks and Alerts

[fatherjack]

Hi,

I am having trouble with locks and performance issues in one application. The SQL server is specific to this application. What settings should i choose to set an alert to let me know when i get too many locks? the locks i am interested in ar ethe ones that show up in SQL EM. ie where a process is blocking another process(es).

I have tried New Alert | lock requests /sec but that is a huge figure when the server is running OK, so i cant guess what value to set for the counter. Is there a better object/counter i should use?

thanks
fatherjack

[rmiao]

Don't think there is any blocking event you can set in alert. But can query blocked column in master..sysprocesses, there is blocking if that column is not 0. You can run the query as scheduled job and send you message if there is blocking.

[fatherjack]

thats a bit awkward, would even say inconsiderate!!!!!

wouldn't it be a big performance hit if i set a scheduled job with too brief a frequency??

is it me or does a column called Blocked not tie in with the fact that it is "Blocking"? :-/

[rmiao]

Another thing can try is set alert on lock wait time.

[MAK]

http://www.databasejournal.com/featu...le.php/3426431

[fatherjack]

Mak,

this looks good. What is the overhead on the server with the job running every minute?