best way to detect locking

Question

Post reply

best way to detect locking

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Jack,

you said:

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

Where did you configure this? I still can't get my alert to fire and it should have today. My BufferHitCacheRation was well below 95 for a significant time, and the alert never fired.

Your help is greatly appreciated here as I would really like to understand and configure some performance alerts.

Thanks,

Lynn

😎

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

DBA-640728

SSChampion

Points: 12898

April 4, 2008 at 1:21 pm

#798657

i did it with the criteria of 40 and started firing one after the other, that's why i was asking you what would be a good number.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

Lynn,

Attached is a Word Doc with screen shots of my Alert.

Here is the Alert scripted by SSMS:



USE [msdb]

GO

/****** Object:  Alert [Test]    Script Date: 04/04/2008 15:06:58 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Test',

		@message_id=0,

		@severity=0,

		@enabled=0,

		@delay_between_responses=300,

		@include_event_description_in=1,

		@category_name=N'[Uncategorized]',

		@performance_condition=N'SQLServer:Locks|Lock Requests/sec|_Total|>|40',

@job_id=N'00000000-0000-0000-0000-000000000000'

I can't seem to get an alert based on BUfferCacheHitRatio to work either. I have tried adding the alert with if Falls Below 100 and I am not getting any alerts generated.

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

DBA (4/4/2008)

i did it with the criteria of 40 and started firing one after the other, that's why i was asking you what would be a good number.

You should run PerfMon for awhile to see what you are getting for peaks and averages then set your alert to that level.

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Earlier today my BufferHitCacheRation was down to an average 75 for a good 30 minutes, and the alert never fired.

We did find the solution to our slow PO performance issue; we found a query that was doing a clustered index scan over a very wide table with approximately 250,000 rows. Put an index on the columns used in the where clause and BANG, everything is working great and the users are very happy now (the lynching party became a celebration so to speak).

😎