XEvents – Scripts Center

Systemhealth xevents : deadlock reports

Cette requête vous permet d’extraire les évènements « deadlock report » de la session d’évènements étendus « system health » :

/*-------------------------------------------------------------------
[SCRIPT] Extract deadlock reports from system_health xevent file
[DATABASE] 
[DESCRIPTION] 
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ]  20200112
-------------------------------------------------------------------*/
with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
   from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where object_name = 'xml_deadlock_report'
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc;

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

[SCRIPT] Extract deadlock reports from system_health xevent file

[DATABASE]

[DESCRIPTION]

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] 20200112

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

with XmlDeadlockReports as

(

select convert(xml, event_data) as EventData

from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)

where object_name = 'xml_deadlock_report'

)

select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,

EventData.query('event/data/value/deadlock') as XdlFile

from XmlDeadlockReports

order by TimeStamp desc;

La colonne « xdl file » contient très exactement le contenu d’un fichier de rapport .xdl : il suffit donc d’enregistrer le xml avec cette extension et de rouvrir ce fichier avec SSMS pour […]

XEvents

SQL User Errors

Ce script permet de configurer une session d’évènements étendus pour capturer les évènements d’erreurs utilisateur.

/*-------------------------------------------------------------------
[SCRIPT] User Errors xevent session   
[DATABASE] master
[DESCRIPTION] create an xevent session for user SQL Errors
[MAJ PAR] DATAFLY - Arian Papillon
[DATE] 20190128
-------------------------------------------------------------------*/
USE master;
GO
DECLARE @XELPath NVARCHAR(MAX)
      , @SQLCmd NVARCHAR(MAX);
SELECT @XELPath = [path] + 'SQLUserErrors.XEL'
FROM   sys.dm_os_server_diagnostics_log_configurations;
SET @SQLCmd = 'CREATE EVENT SESSION SQLUserErrors
    ON SERVER
    ADD EVENT sqlserver.error_reported
    (
     -- ACTION( sqlserver.tsql_stack )
     ACTION ( sqlserver.tsql_stack
            , sqlserver.sql_text
            , sqlserver.database_id
            , sqlserver.username
            , sqlserver.client_app_name
            , sqlserver.client_hostname )
     WHERE ((   [error_number] <> 2528 -- DBCC execution completed...
                AND [error_number] <> 3014 -- BACKUP LOG successfully processed ...
                AND [error_number] <> 3262 -- The backup set on file is valid ...
                AND [error_number] <> 4035 -- Processed 0 pages for database ...
                AND [error_number] <> 5701 -- Changed database context to ,,,
                AND [error_number] <> 5703 -- Changed language setting to ...
                AND [error_number] <> 18265 -- Log was backed up. ...
                AND [error_number] <> 8153 -- NULL Aggregate. ...
                AND [error_number] <> 3211 -- Progression message. ...
                AND [error_number] <> 14205 -- (unknown)

                AND [error_number] <> 14213 -- Core Job Details:
                AND [error_number] <> 14214 -- Job Steps:
                AND [error_number] <> 14215 -- Job Schedules:
                AND [error_number] <> 14216 -- Job Target Servers:
                AND [error_number] <> 14549 -- (Description not requested.)
                AND [error_number] <> 14558 -- (encrypted command)
                AND [error_number] <> 14559 -- (append output file)
                AND [error_number] <> 14560 -- (include results in history)
                AND [error_number] <> 14561 -- (normal)
                AND [error_number] <> 14562 -- (quit with success)
                AND [error_number] <> 14563 -- (quit with failure)
                AND [error_number] <> 14564 -- (goto next step)
                AND [error_number] <> 14565 -- (goto step)
                AND [error_number] <> 14566 -- (idle)
                AND [error_number] <> 14567 -- (below normal)
                AND [error_number] <> 14568 -- (above normal)
                AND [error_number] <> 14569 -- (time critical)
                AND [error_number] <> 14570 -- (Job outcome)
                AND [error_number] <> 17550 -- DBCC Information
                AND [error_number] <> 17551 -- DBCC Information

                AND [error_number] <> 14635 -- Mail queued.
                AND [error_number] <> 14638 -- Activation successful.
                AND [error_number] <> 15477 -- Rename object
 		AND [error_number] <> 9911  -- alimentation Auto de texte intégral commencée
		AND [error_number] <> 22866 -- Informations Change Tracking
                AND [error_number] < 50000 -- Exclude User Errors

                --  AND [database_id] = 8
                --  AND ( [client_hostname] <> ''HOSTXYZ'' 
		)))
    -- Target File Mode
    ADD TARGET package0.asynchronous_file_target
    ( SET filename = ''' + @XELPath
              + ''', max_file_size = 256, max_rollover_files = 4 )
    WITH ( MAX_MEMORY = 4096KB
         , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
         , MAX_DISPATCH_LATENCY = 1 SECONDS
         , MEMORY_PARTITION_MODE = NONE
         , TRACK_CAUSALITY = ON -- Very important to get the event in order later on
         , STARTUP_STATE = ON   -- Note that we start on
    );';
EXEC sp_executesql @SQLCmd;

-- Start the session 
IF EXISTS (   SELECT *
              FROM   sys.server_event_sessions
              WHERE  name = 'SQLUserErrors' )
    ALTER EVENT SESSION SQLUserErrors
        ON SERVER STATE = START;
ELSE
    RAISERROR(
        'ERROR : XEventSession SQLUserError NOT created, cannot start' , 19, 1) WITH LOG;
GO

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

[SCRIPT] User Errors xevent session

[DATABASE] master

[DESCRIPTION] create an xevent session for user SQL Errors

[MAJ PAR] DATAFLY - Arian Papillon

[DATE] 20190128

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

USE master;

DECLARE @XELPath NVARCHAR(MAX)

, @SQLCmd NVARCHAR(MAX);

SELECT @XELPath = [path] + 'SQLUserErrors.XEL'

FROM sys.dm_os_server_diagnostics_log_configurations;

SET @SQLCmd = 'CREATE EVENT SESSION SQLUserErrors

ON SERVER

ADD EVENT sqlserver.error_reported

(

-- ACTION( sqlserver.tsql_stack )

ACTION ( sqlserver.tsql_stack

, sqlserver.sql_text

, sqlserver.database_id

, sqlserver.username

, sqlserver.client_app_name

, sqlserver.client_hostname )

WHERE (( [error_number] <> 2528 -- DBCC execution completed...

AND [error_number] <> 3014 -- BACKUP LOG successfully processed ...

AND [error_number] <> 3262 -- The backup set on file is valid ...

AND [error_number] <> 4035 -- Processed 0 pages for database ...

AND [error_number] <> 5701 -- Changed database context to ,,,

AND [error_number] <> 5703 -- Changed language setting to ...

AND [error_number] <> 18265 -- Log was backed up. ...

AND [error_number] <> 8153 -- NULL Aggregate. ...

AND [error_number] <> 3211 -- Progression message. ...

AND [error_number] <> 14205 -- (unknown)

AND [error_number] <> 14213 -- Core Job Details:

AND [error_number] <> 14214 -- Job Steps:

AND [error_number] <> 14215 -- Job Schedules:

AND [error_number] <> 14216 -- Job Target Servers:

AND [error_number] <> 14549 -- (Description not requested.)

AND [error_number] <> 14558 -- (encrypted command)

AND [error_number] <> 14559 -- (append output file)

AND [error_number] <> 14560 -- (include results in history)

AND [error_number] <> 14561 -- (normal)

AND [error_number] <> 14562 -- (quit with success)

AND [error_number] <> 14563 -- (quit with failure)

AND [error_number] <> 14564 -- (goto next step)

AND [error_number] <> 14565 -- (goto step)

AND [error_number] <> 14566 -- (idle)

AND [error_number] <> 14567 -- (below normal)

AND [error_number] <> 14568 -- (above normal)

AND [error_number] <> 14569 -- (time critical)

AND [error_number] <> 14570 -- (Job outcome)

AND [error_number] <> 17550 -- DBCC Information

AND [error_number] <> 17551 -- DBCC Information

AND [error_number] <> 14635 -- Mail queued.

AND [error_number] <> 14638 -- Activation successful.

AND [error_number] <> 15477 -- Rename object

AND [error_number] <> 9911 -- alimentation Auto de texte intégral commencée

AND [error_number] <> 22866 -- Informations Change Tracking

AND [error_number] < 50000 -- Exclude User Errors

-- AND [database_id] = 8

-- AND ( [client_hostname] <> ''HOSTXYZ''

)))

-- Target File Mode

ADD TARGET package0.asynchronous_file_target

( SET filename = ''' + @XELPath

+ ''', max_file_size = 256, max_rollover_files = 4 )

WITH ( MAX_MEMORY = 4096KB

, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 1 SECONDS

, MEMORY_PARTITION_MODE = NONE

, TRACK_CAUSALITY = ON -- Very important to get the event in order later on

, STARTUP_STATE = ON -- Note that we start on

);';

EXEC sp_executesql @SQLCmd;

-- Start the session

IF EXISTS ( SELECT *

FROM sys.server_event_sessions

WHERE name = 'SQLUserErrors' )

ALTER EVENT SESSION SQLUserErrors

ON SERVER STATE = START;

ELSE

RAISERROR(

'ERROR : XEventSession SQLUserError NOT created, cannot start' , 19, 1) WITH LOG;

Pour obtenir les évènements capturés sous forme tabulaire, utiliser le script suivant :

/*-------------------------------------------------------------------
[SCRIPT] read SQL User Errors xevents   
[DATABASE] master
[MAJ PAR] DATAFLY - Arian Papillon
[DATE] 20190128
-------------------------------------------------------------------*/
SET NOCOUNT ON;
DECLARE @outputfile NVARCHAR(max)
SELECT @outputfile = [path] + 'SQLUserErrors'
FROM   sys.dm_os_server_diagnostics_log_configurations;
-- Récupère les évènements
IF ( OBJECT_ID('tempdb..#EventXML') IS NOT NULL )
    DROP TABLE #EventXML;
DECLARE @path NVARCHAR(260) = @outputfile + '*.XEL'
      , @mdpath NVARCHAR(260) = @outputfile + '*.xem'
      , @initial_file_name NVARCHAR(260) = NULL
      , @initial_offset BIGINT = NULL;
SELECT IDENTITY(INT, 1, 1) AS ID
     , *
     , CAST(REPLACE(E.event_data, CHAR(3), '?') AS XML) AS X
INTO   #EventXML
FROM   master.sys.fn_xe_file_target_read_file(
           @path ,@mdpath, @initial_file_name, @initial_offset) E;
-- Tabularise le XML
SELECT node.value('./@timestamp', 'datetime') AS event_time
     , node.value('./@name', 'varchar(4000)') AS event_name
     , node.value('(data[@name="message"]/value)[1]', 'varchar(4000)') AS Messsage
     , node.value('(data[@name="error_number"]/value)[1]', 'int') AS Error_Value
     , node.value('(data[@name="severity"]/value)[1]', 'int') AS Severity
     , node.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS SQL_Text
     , node.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id
     , node.value(
           '(action[@name="client_hostname"]/value)[1]' ,'varchar(128)') AS hostname
     , node.value(
           '(action[@name="client_app_name"]/value)[1]' ,'varchar(128)') AS appname
     , node.value('(action[@name="username"]/value)[1]', 'varchar(128)') AS username
     --     , #EventXML.*
     , DB_NAME(node.value('(action[@name="database_id"]/value)[1]', 'int')) AS database_name
FROM   #EventXML
       CROSS APPLY #EventXML.x.nodes('//event') n(node)
	   ORDER BY event_time

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

[SCRIPT] read SQL User Errors xevents

[DATABASE] master

[MAJ PAR] DATAFLY - Arian Papillon

[DATE] 20190128

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

SET NOCOUNT ON;

DECLARE @outputfile NVARCHAR(max)

SELECT @outputfile = [path] + 'SQLUserErrors'

FROM sys.dm_os_server_diagnostics_log_configurations;

-- Récupère les évènements

IF ( OBJECT_ID('tempdb..#EventXML') IS NOT NULL )

DROP TABLE #EventXML;

DECLARE @path NVARCHAR(260) = @outputfile + '*.XEL'

, @mdpath NVARCHAR(260) = @outputfile + '*.xem'

, @initial_file_name NVARCHAR(260) = NULL

, @initial_offset BIGINT = NULL;

SELECT IDENTITY(INT, 1, 1) AS ID

, *

, CAST(REPLACE(E.event_data, CHAR(3), '?') AS XML) AS X

INTO #EventXML

FROM master.sys.fn_xe_file_target_read_file(

@path ,@mdpath, @initial_file_name, @initial_offset) E;

-- Tabularise le XML

SELECT node.value('./@timestamp', 'datetime') AS event_time

, node.value('./@name', 'varchar(4000)') AS event_name

, node.value('(data[@name="message"]/value)[1]', 'varchar(4000)') AS Messsage

, node.value('(data[@name="error_number"]/value)[1]', 'int') AS Error_Value

, node.value('(data[@name="severity"]/value)[1]', 'int') AS Severity

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

, node.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id

, node.value(

'(action[@name="client_hostname"]/value)[1]' ,'varchar(128)') AS hostname

, node.value(

'(action[@name="client_app_name"]/value)[1]' ,'varchar(128)') AS appname

, node.value('(action[@name="username"]/value)[1]', 'varchar(128)') AS username

-- , #EventXML.*

, DB_NAME(node.value('(action[@name="database_id"]/value)[1]', 'int')) AS database_name

FROM #EventXML

CROSS APPLY #EventXML.x.nodes('//event') n(node)

ORDER BY event_time

XEvents

System health locks

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;