This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Metric - Deadlocks

Description

This metric tracks the number of deadlocks from the system_health extended event session.

Metric Definition

To create the metric, click on Options > Custom Resource Metrics and configure similar to below.

Database Versions: 9.0(SQL 2008 and SQL 2008R2)

Display Name: Deadlocks

Category: Sessions

Units: Deadlocks

Metric Type: Single Value

Frequency: 600 (once every 10 minutes)

Timeout: 20

SQL Script:

DECLARE @XMLData xml

SELECT TOP 1 @XMLData = CAST(target_data AS xml)

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s

ON s.address = st.event_session_address

WHERE name = 'system_health'

SELECT count(1)

FROM @XMLData.nodes('//event[@name="xml_deadlock_report"]') AS X (Col)

WHERE col.value('(data/value)[1]', 'VARCHAR(MAX)') LIKE '<deadlock%'

AND DATEDIFF(MINUTE, col.value('(./@timestamp)', 'DATETIME'), GETDATE()) < 10.0

  • Hi, are this metric like those we find in NPM Atlas when we want to set thresholds to get status update (critical, warning...) for an object?

    Because I have this UnDP that I vant to put in NPM Atlas, but it shows me that there's no metrics assigned to my UnDP, so I can't set thresholds to update its status in the map.

    if this matches my case can you help me with this? My UndP poles 2 states: Open or Closed, I would like to do it this way: Closed --> Critical, and Open --> Normal

    thanks.

  • What you are looking at with the Deadlock metric here is specific to another tool within the SolarWinds suite called DPA (Database Performance Analyzer).  It's specific to deadlocks within a database instance.  I'd recommend you re-post your question under the NPM tool forum as I think it's more specific to that product.  Let us know if there's anything we can help you with regarding DPA on this thread though.  Thanks!

  • The query does sense deadlocks, but seems to be a count of deadlocks over the last six hours, and only decreases if there have been no more deadlocks for six hours.  At least that's how it acts on our instances.  So I set the metric's Display Name to "Deadlocks (in 6 hours)" and Description to "Total deadlocks in the last 6 hours."

  • The resulting metric graph is actually useful if you keep in mind that the count is for a rolling window of about six to eight hours.  The window size seems to vary among instances.  So now I set the metric's Display Name back to just "Deadlocks" and the Description to "Total number of deadlocks in the last 6 hours or so. (Unfortunately the rolling window is fuzzy.)".  That is fair enough, since the Description only pops up upon mousing over the Display Name.

  • As an alternative metric the below will work with SQL2005;

    Metric Type: Delta
    Frequency: 600 (once every 10 minutes)
    Timeout: 20

    SELECT cntr_value as count
    FROM sys.dm_os_performance_counters with (nolock)
    WHERE object_name = 'SQLServer:Locks'
    AND counter_name = 'Number of Deadlocks/sec'
    AND instance_name = '_Total'

    Note - you might have to restart the Ignite PI Server service to get Delta metrics to work after creation

  • Also keep in mind you may want to set the deadlock trace flags so that the details of the deadlock get written to the error log.

  • will this custom metric applies to SQL 2012 above version?

  • that's weird not seeing deadlock for SQL 2012 SQL instances.. Should I changed the database version to All Versions, instead of limiting this to 9.0(SQL 2008 and SQL 2008R2) as indicated in this thread?

  • Do you have the option set to "and above"?