# MSSQL Deadlocks metric

**URL:** <https://discuss.elastic.co/t/mssql-deadlocks-metric/216869>\
**Category:** Beats\
**Tags:** metricbeat\
**Created:** [January 28, 2020, 3:51pm UTC](https://discuss.elastic.co/t/mssql-deadlocks-metric/216869 "2020-01-28T15:51:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![razgrim](https://avatars.discourse-cdn.com/v4/letter/r/f19dbf/32.png) [@razgrim](https://discuss.elastic.co/u/razgrim)\
**Post date:** [January 28, 2020, 3:51pm UTC](https://discuss.elastic.co/t/mssql-deadlocks-metric/216869/1 "2020-01-28T15:51:43Z")

</div>

Proposing feature per [https://www.elastic.co/guide/en/beats/devguide/current/beats-contributing.html](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](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.

---

<div class="post-metadata">

**Author:** ![razgrim](https://avatars.discourse-cdn.com/v4/letter/r/f19dbf/32.png) [@razgrim](https://discuss.elastic.co/u/razgrim)\
**Post date:** [January 28, 2020, 4:06pm UTC](https://discuss.elastic.co/t/mssql-deadlocks-metric/216869/2 "2020-01-28T16:06:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kaiyan\_Sheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kaiyan_sheng/32/38247_2.png) [@Kaiyan\_Sheng](https://discuss.elastic.co/u/Kaiyan_Sheng)\
**Post date:** [January 28, 2020, 11:27pm UTC](https://discuss.elastic.co/t/mssql-deadlocks-metric/216869/3 "2020-01-28T23:27:39Z")

</div>

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](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](https://github.com/elastic/beats/blob/128787066976ae70630b9379436e5b709135d744/x-pack/metricbeat/module/mssql/performance/data_integration_test.go#L19).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 25, 2020, 11:27pm UTC](https://discuss.elastic.co/t/mssql-deadlocks-metric/216869/4 "2020-02-25T23:27:43Z")

</div>

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