MSSQL Deadlocks metric

Elastic Stack Beats
1

Proposing feature per https://www.elastic.co/guide/en/beats/devguide/current/beats-contributing.html

Our DBA is looking to have MSSQL deadlocks included in metricbeat mssql performance metricset.
The following query gets this data:
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total'

I'm devops, not an experienced dev or familiar with GO, however it seems like everything should work if I just merge this into the query contained in https://github.com/elastic/beats/blob/128787066976ae70630b9379436e5b709135d744/x-pack/metricbeat/module/mssql/performance/performance.go

SELECT object_name,
counter_name,
instance_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec'
OR counter_name = 'SQL Re-Compilations/sec'
OR counter_name = 'User Connections'
OR counter_name = 'Page splits/sec'
OR ( counter_name = 'Lock Waits/sec'
AND instance_name = '_Total' )
OR ( counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total' )
OR counter_name = 'Page splits/sec'
OR ( object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy' )
OR counter_name = 'Batch Requests/sec'
OR ( counter_name = 'Buffer cache hit ratio'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Target pages'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Database pages'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Checkpoint pages/sec'
AND object_name = 'SQLServer:Buffer Manager' )
OR ( counter_name = 'Lock Waits/sec'
AND instance_name = '_Total' )
OR ( counter_name = 'Transactions'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Logins/sec'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Logouts/sec'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Connection Reset/sec'
AND object_name = 'SQLServer:General Statistics' )
OR ( counter_name = 'Active Temp Tables'
AND object_name = 'SQLServer:General Statistics' )

Will do my best to figure out how to test this out and submit a pull request for this change.

1 Like
2

Will have to add to https://github.com/elastic/beats/blob/master/x-pack/metricbeat/module/mssql/performance/data.go as well.

3

Hi @razgrim, thanks for reaching out. Yes please feel free to create a PR for this. I think you can add one more OR after https://github.com/elastic/beats/blob/128787066976ae70630b9379436e5b709135d744/x-pack/metricbeat/module/mssql/performance/performance.go#L108?

For performance/data.go, it is generated by TestData function in https://github.com/elastic/beats/blob/128787066976ae70630b9379436e5b709135d744/x-pack/metricbeat/module/mssql/performance/data_integration_test.go#L19.

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.