DB Average Wait Time is too high

Question

DB Average Wait Time is too high

DBA-640728

SSChampion

Points: 12898
More actions
October 31, 2013 at 11:07 am

#281879

hi guys, i am currently working with setting up scom alerts for my sql 2008 r2 enterprise instance. There is an alert " SQL 2008 DB Average Wait Time is too high" , the threshold at this time is set up to be 200 ms for all the instances, therefore iam getting alerts every hour for same and different sql instances. I would like to set up some type of baseline for my systems to be able to change the threshold per instance, what would be the best way? . I was trying to get information from set sys.dm_os_wait_stats but i get the total wait time.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

What I do is set up performance monitor to collect this data. Check around in perfmon to see what counters you want to get a baseline of, and then you should be able to collect a day, or even a week of a few counters (say a dozen) with no major impact.

S. Kusen SSChampion Points: 10936 More actions · Answer 2

I came across this link describing a problem with two rules:

http://social.technet.microsoft.com/Forums/systemcenter/en-US/ffeefe0d-0ef7-49a3-862e-9be27989dc5d/scom2012-alert-sql-2008-db-average-wait-time-recompilationis-too-high?forum=operationsmanagergeneral

specifically:

YES there have been bugs reported with this MP.
You should disable the following rules:
MSSQL 2012: Collect DB Engine CPU Utilization (%)
MSSQL 2008: Collect DB Engine CPU Utilization (%)
The following monitors:
CPU Utilization (%) for 2008
CPU Utilization (%) for 2012
For more on this please see:
http://social.technet.microsoft.com/Forums/systemcenter/en-US/d2a4fff0-038e-4970-9eff-d1fa48c94a34/sql-server-mp-6400-operations-manager-failed-to-convert-performance-data
http://social.technet.microsoft.com/Forums/en-US/d2a4fff0-038e-4970-9eff-d1fa48c94a34/sql-server-mp-6400-operations-manager-failed-to-convert-performance-data
Scott Moss MVP (Operations Manager) President - System Center Virtual Users Group |Vice President - Atlanta Southeast Management Users Group (ATL SMUG)
Please remember to click “Mark as Answer” on the post that helps you!
my new blog om2012.wordpress.com

Dominique DUCHEMIN SSChasing Mays Points: 604 More actions · Answer 3

Hello,

I know it is an old post but any updates on this issue?

I have SQL MP 6.4.1 and the alerts are flooding if the rules/monitors are enabled...

Thanks,

DOm

S. Kusen SSChampion Points: 10936 More actions · Answer 4

I wound up just leaving them disabled as I couldn't find any value with them compared to the out of the box CPU utilization. Since we mostly have dedicated DB engine servers, the CPU utilization is always just on SQL anyway. I enabled the MDW on some of the instances where we were getting the average DB wait time alert, and I could never find anything concrete to "fix" to prevent the alert from coming. In my opinion, that alert was pretty much useless.

iv69 Old Hand Points: 386 More actions · Answer 5

Try to use Delta to calculate Average Wait Time per second:

something like this :

select * into #temp1 from sys.dm_os_performance_counters

where counter_name like 'Average Wait Time (ms)%'

waitfor delay '00:00:30'

select * into #temp2 from sys.dm_os_performance_counters

where counter_name like 'Average Wait Time (ms)%'

select *,(#temp2.cntr_value-#temp1.cntr_value)/30 from #temp1 join #temp2 on #temp2.instance_name=#temp1.instance_name

where #temp2.cntr_value>#temp1.cntr_value

iv69 Old Hand Points: 386 More actions · Answer 6

I want to correct myself better to use base counter for Average Wait time:

select AverageWaitTime for _Total instance and divide to Average Wait time Base for _Total instance.

there are some counters that use delta but this is not of such type.

Perveen.khan 33342 SSC Journeyman Points: 97 More actions · Answer 7

DBA-640728 - Thursday, October 31, 2013 11:07 AM
hi guys, i am currently working with setting up scom alerts for my sql 2008 r2 enterprise instance. There is an alert " SQL 2008 DB Average Wait Time is too high" , the threshold at this time is set up to be 200 ms for all the instances, therefore iam getting alerts every hour for same and different sql instances. I would like to set up some type of baseline for my systems to be able to change the threshold per instance, what would be the best way? . I was trying to get information from set sys.dm_os_wait_stats but i get the total wait time.