System health locks – Scripts Center

Ce script permet d’interroger les derniers évènements d’attentes de verrous (> 30 secondes) de la session d’évènements étendus system_health (par défaut active en permanence) :

/*-------------------------------------------------------------------
[SCRIPT] system_health locks   
[DATABASE] master
[DESCRIPTION] get locks events from xevent system_health session
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
DECLARE @x XML = (   SELECT CAST(xet.target_data AS XML)
                     FROM   sys.dm_xe_session_targets xet
                            JOIN sys.dm_xe_sessions xe ON ( xe.address = xet.event_session_address )
                     WHERE  xe.name = 'system_health'
                            AND xet.target_name = 'ring_buffer' );
SELECT   TOP ( 100 ) t.e.value('@name', 'varchar(50)') AS EventName
                   , DATEADD(hh, 1, t.e.value('@timestamp', 'datetime')) AS DateAndTime -- heure locale, ajout d'une heure
                   , t.e.value(
                         '(data[@name="wait_type"]/text)[1]' , 'VARCHAR(100)') AS [Wait Type]
                   , CONVERT(
                         FLOAT
                       , t.e.value(
                             '(data[@name="duration"]/value)[1]' , 'BIGINT'))
                     / 1000 AS [Duration (s)]
                   , t.e.value('(action[@name="session_id"]/value)[1]', 'int') AS [spid]
                   , t.e.value(
                         '(action[@name="sql_text"]/value)[1]' , 'varchar(max)') AS sql_text
FROM     @x.nodes('//RingBufferTarget/event') AS t(e)
WHERE    t.e.value('@name', 'varchar(50)') = 'wait_info'
         AND t.e.value('(data[@name="wait_type"]/text)[1]', 'VARCHAR(100)') LIKE 'LCK%'
ORDER BY t.e.value('@timestamp', 'datetime') DESC;

/*-------------------------------------------------------------------

[SCRIPT] system_health locks

[DATABASE] master

[DESCRIPTION] get locks events from xevent system_health session

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

DECLARE @x XML = ( SELECT CAST(xet.target_data AS XML)

FROM sys.dm_xe_session_targets xet

JOIN sys.dm_xe_sessions xe ON ( xe.address = xet.event_session_address )

WHERE xe.name = 'system_health'

AND xet.target_name = 'ring_buffer' );

SELECT TOP ( 100 ) t.e.value('@name', 'varchar(50)') AS EventName

, DATEADD(hh, 1, t.e.value('@timestamp', 'datetime')) AS DateAndTime -- heure locale, ajout d'une heure

, t.e.value(

'(data[@name="wait_type"]/text)[1]' , 'VARCHAR(100)') AS [Wait Type]

, CONVERT(

FLOAT

, t.e.value(

'(data[@name="duration"]/value)[1]' , 'BIGINT'))

/ 1000 AS [Duration (s)]

, t.e.value('(action[@name="session_id"]/value)[1]', 'int') AS [spid]

, t.e.value(

'(action[@name="sql_text"]/value)[1]' , 'varchar(max)') AS sql_text

FROM @x.nodes('//RingBufferTarget/event') AS t(e)

WHERE t.e.value('@name', 'varchar(50)') = 'wait_info'

AND t.e.value('(data[@name="wait_type"]/text)[1]', 'VARCHAR(100)') LIKE 'LCK%'

ORDER BY t.e.value('@timestamp', 'datetime') DESC;