Include Lock:Timeout (timeout > 0) Event Class in sqlserver input

[sawo1337]

Feature Request

The current metric only shows Lock Timeouts/sec, which includes internal lock probes that may not be a problem at all. In a live environment, you may get 100s of lock timeouts/sec, but actually no lock timeouts that are longer than 0.

Proposal:

Add Lock Timeouts (timeout > 0)/sec to the sqlserver_performance metric

Current behavior:

Only Lock Timeouts/sec is being gathered

Desired behavior:

Add Lock Timeouts (timeout > 0)/sec to the sqlserver_performance metric

Use case:

If you want to only see locks that are actually causing issues, it is better to skip locks where the timeout is 0. From MSDN:

MSDN Article

[danielnelson]

Seems like a good idea to me, what do you think @denzilribeiro?

[denzilribeiro]

What we have is just a perf counter in which Lock timeout/sec is a rate counter you cannot just ignore /exclude values. Look at the cntr_type column in sys.dm_os_performance_counters
To use waitstats analysis or waits on LCK to troubleshoot any lock related issue/s. I am not totally sure if you are proposing adding XEvent class? that is more tracing not monitoring.

[sawo1337]

There is a separate counter that is actually named "Lock Timeouts (timeout > 0)/sec" - it is available by default, it is just a matter of including it as it gives much more realistic values.

[denzilribeiro]

That is totally fine to replace this one with that .

[sawo1337]

Exactly, here is an output from production server with pretty light load:

Lock timeouts is giving pretty crazy values due to internal locks, but the actual locks with wait over 0 is giving some reasonable output. This is why Microsoft suggests watching Lock Timeouts (timeout > 0)/sec instead.

[sawo1337]

Proposed change in sqlserver.go:
line 639:
'Lock Timeouts/sec',
becomes:
'Lock Timeouts (timeout > 0)/sec,
Or add this as an additional counter on line 640 and keep the existing counter at line 639

[danielnelson]

@sawo1337 Can you open a pull request with both counters?