best way to detect locking
-
hi guys, is the best way to detect locking looking at the activity monitor or through the profiler?
also i was reading there is a Blocked Process Report Event Class, have you used the before?
is there a way to send an alert with the locks?
-
-
http://msdn2.microsoft.com/en-us/library/ms191168.aspx
let me know your thoughts please.
-
Thank you for your post. I will let you know as soon as I read it!
-
Activity monitor is just giving you a point in time snapshot while profiler will give you a pattern of locking behavior. You can also use Perfomance Monitor and the SQL Server:Locks objects.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
is there any way to create an alert when there is locks?
-
Yes. In SSMS go to SQL Server Agent -> Alerts. Right-Click and select New Alert. In the Type drop-down select SQL Server Performance Condition Alert. Then you can select the Object , COunter, Instance, and the Alert criteria.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Great Tip!
-
DBA, excuse me for not replying yet, but I am having some problems at work.
-
thank you!! which criteria is best? do you have an alert for every single counter?
-
Jack,
I tried creating an Alert, but it doesn't seem to fire. Is there something else that needs to be done that I may be missing? I was sure I followed the same instructions you provided earlier in this thread.
😎
-
Lynn,
I actually had never placed an alert on performance condition, but I knew "how" to do it. I just created one on my local SQL Server on:
SQLServer:Locks - Lock Requests/sec - _Total
My criteria was set to when rises above 0 just to see if it would fire. I then ran a Select with(TabLOckX) and immediatly got an email with this text:
DATE/TIME:4/3/2008 2:13:02 PM
DESCRIPTION:The SQL Server performance counter 'Lock Requests/sec' (instance '_Total') of object 'SQLServer:Locks' is now above the threshold of 0.00 (the current value is 61.75).
COMMENT:(None)
JOB RUN:(None)
U have it configured to respond via email at 5 minute intervals and it is. I actually modified it to rises about 40 and I just got another. You can check the history to see if you are ever meeting the condition.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
so a good value is above 40?
-
Jack,
Okay, I'll have to reverify how I have it setup tomorrow. I'm not at work today due to car problems (darned thing was running fine last night, but this morning I couldn't even get out of our cul-d-sac.
).
Thanks!
😎
-
DBA (4/3/2008)
so a good value is above 40?Any value you want to be aware of is going to vary based on your baseline and setup. As I said, this is just running on my desktop with SQL Server 2005 Dev edition. I set up the alert to make sure it worked. On my desktop which is not being accessed by anyone but me the lock request/sec has been as high as 3000. This is mainly caused just by having SSRS installed and running. Also this counter was one I chose knowing I would get some results. Remember locks are necessary in any RDBMS. You should get an idea of what is "normal" locking activity for your system and then you can look at it when there is a slowdown reported.
A good resource for this is Inside Microsoft Sql Server 2005 Query Tuning and Optimization by Kalen Delaney, et al.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply