MSSQL - Incorrect lock wait time value

[ssi.anthony.deguia]

Hi Nagios Support,

We would like to consult the incorrect value with regards to the MSSQL wizard.

We use MSSQL Lock Wait Average Time checking with our servers and reached the critical value. According to our database admin, these values are incorrect. It seems that the MSSQL plugin was computing the sum of the lock wait time.

This was the screenshot of the error.

deca-lock-clean-1.png

This was the check_mssql_server.py version.
# Version 2.0.2

This was the Nagios Server version


Kindly advise.

Thank you.

[ssax]

This is the SQL query that it is running, have your DBA run this query and post the results:

Code: Select all

SELECT cntr_value FROM sysperfinfo WHERE counter_name='Lock Wait Time (ms)' AND instance_name='_Total';

[ssi.anthony.deguia]

Hi Nagios Support,

We execute on our database using the script “check_mssql_server.py”. Lock Request value change depends on sql transactions.

CRITICAL: Lock Requests / Sec is 310241.007685/sec|lock_requests=310241.007685;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;
OK: Lock Requests / Sec is 457.746607991/sec|lock_requests=457.746607991;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;
OK: Lock Requests / Sec is 194.174897515/sec|lock_requests=194.174897515;110000;120458;;;
OK: Lock Requests / Sec is 0.0/sec|lock_requests=0.0;110000;120458;;;


Lock Wait Average Time doesn’t change, it means Nagios report the overall total of wait read from the database.
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;
CRITICAL: Lock Wait Average Time (ms) is 274588975.0ms|lockwait=274588975.0ms;110000;120458;;;


The query “SELECT cntr_value FROM sysperfinfo WHERE counter_name='Lock Wait Time (ms)' AND instance_name='_Total';” is to get the total, but nagios report it as average which is not.

'lockwait' : { 'help' : 'Lock Wait Average Time (ms)',
'stdout' : 'Lock Wait Average Time (ms) is %sms',
'label' : 'lockwait',
'unit' : 'ms',
'query' : INST_QUERY % ('Lock Wait Time (ms)', '_Total'),
'type' : 'standard',
},

Kindly advise.

Thank you very much.

[ssax]

Ok, I see what you're saying, it looks like the text is wrong and should really just say "Lock Wait Time" instead of "Lock Wait Average Time" because it is not an average, it's a total.

The average is actually the "MSSQL Average Wait Time" service, is that what you are looking for? I just want to make sure that I'm understanding what you're trying to achieve/you're problem.

The locks object counters can be referenced here: https://technet.microsoft.com/en-us/lib ... 10%29.aspx

I will talk with the developers on this and see if that text should be changed.

[ssax]

I talked with the developers and they agreed that the text was misleading and it should be removed in the next release of the wizard plugin.

[ssi.anthony.deguia]

Hi ssax,

Thank you for handling my concern with regards to the MSSQL plug-in.

You can now lock this thread.

I'll just check the MSSQL Server wizard page for updates.

Your help was very much appreciated.

[ssax]

No problem, thank you for pointing it out. Marking as resolved and locking now.