расширенных событий SQL Server для выявления запросов, которые выполняются слишком долго.

Административные скрипты MS SQL > расширенных событий SQL Server для выявления запросов, которые выполняются слишком долго.
13.02.2019 22:03:54


Наиболее часто встречающиеся слова в статье:

[objlocks] [sqlserver] [''bigint''] [package0] [database_name] [nt_username] [session_id] [sql_text] [''varchar] [RBufXml]


Статья:

--Этот шаблон соответствует шаблону "TSQL_Duration" в профилировщике. Шаблон записывает сведения обо всех инструкциях Transact-SQL, которые клиенты отправляют серверу SQL Server и о времени выполнения этих инструкций (в микросекундах). 
--Используйте этот шаблон для выявления запросов, которые выполняются слишком долго.  >5 сек
CREATE EVENT SESSION [TSQL_Duration] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(package0.process_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(5000000)))),
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username,sqlserver.client_app_name)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_int64]([duration],(5000000)) AND [package0].[greater_than_uint64]([sqlserver].[database_id],(4))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
 
 
-- start
ALTER EVENT SESSION TSQL_Duration ON SERVER STATE = START;
 
-- stop
ALTER EVENT SESSION TSQL_Duration ON SERVER STATE = STOP;
 
--- просмотр
 
IF OBJECT_ID(N''TempDB..#XmlAsTable'', N''U'') IS NOT NULL
    DROP TABLE #XmlAsTable;
SELECT CAST(LocksAcquired.TargetXml AS XML) AS RBufXml
INTO #XmlAsTable
FROM
(
    SELECT CAST(t.target_data AS XML) AS TargetXml
    FROM sys.dm_xe_session_targets AS t
         JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
    WHERE t.target_name = ''ring_buffer''
          AND s.name = ''TSQL_Duration''
) AS LocksAcquired;
--
 
SELECT duration_sec, 
       cpu_time_sec, 
       nt_username,
       writes,
       logical_reads as [logical_Page_reads], 
       row_count,
       [database_name], 
       sql_text,
       physical_reads,
        dateadd(hh,3,[datas]) as [datas],
        session_id,
        CASE
         WHEN result=2 THEN ''Abort''
         WHEN result=0 THEN ''OK''
        END [result],
        ''sql_statement_completed'' [event]
 
FROM
 
    (SELECT objlocks.value(''(data[@name="duration"]/value)[1]'', ''bigint'')/1000000 AS duration_sec, 
           objlocks.value(''(data[@name="cpu_time"]/value)[1]'', ''bigint'')/1000000 AS cpu_time_sec, 
           objlocks.value(''(action[@name="nt_username"]/value)[1]'', ''varchar(250)'') AS [nt_username], 
           objlocks.value(''(data[@name="writes"]/value)[1]'', ''bigint'') AS [writes],
           objlocks.value(''(data[@name="logical_reads"]/value)[1]'', ''bigint'') AS [logical_reads],
           objlocks.value(''(data[@name="row_count"]/value)[1]'', ''bigint'') AS [row_count],           
           objlocks.value(''(action[@name="database_name"]/value)[1]'', ''varchar(100)'') AS [database_name], 
           objlocks.value(''(action[@name="sql_text"]/value)[1]'', ''varchar(max)'') AS [sql_text],
           objlocks.value(''(data[@name="physical_reads"]/value)[1]'', ''bigint'') AS physical_reads, 
           objlocks.value(''(@timestamp)[1]'', ''datetime'') AS [datas],
           objlocks.value(''(action[@name="session_id"]/value)[1]'', ''int'') AS session_id,
           objlocks.value(''(data[@name="result"]/value)[1]'', ''int'') AS [result],
           RBufXml
    FROM #XmlAsTable heldlocks
         CROSS APPLY RBufXml.nodes(''//event[@name="sql_statement_completed"]'') AS T(objlocks) ) H
 
 UNION 
 
 SELECT duration_sec, 
       cpu_time_sec, 
       nt_username,
       writes,
       logical_reads as [logical_Page_reads], 
       row_count,
       [database_name], 
       sql_text,
       physical_reads,
        dateadd(hh,3,[datas]) as [datas],
        session_id,
        CASE
         WHEN result=2 THEN ''Abort''
         WHEN result=0 THEN ''OK''
        END [result],
        ''sql_batch_completed'' [event]
 
FROM
 
    (SELECT objlocks.value(''(data[@name="duration"]/value)[1]'', ''bigint'')/1000000 AS duration_sec, 
           objlocks.value(''(data[@name="cpu_time"]/value)[1]'', ''bigint'')/1000000 AS cpu_time_sec, 
           objlocks.value(''(action[@name="nt_username"]/value)[1]'', ''varchar(250)'') AS [nt_username], 
           objlocks.value(''(data[@name="writes"]/value)[1]'', ''bigint'') AS [writes],
           objlocks.value(''(data[@name="logical_reads"]/value)[1]'', ''bigint'') AS [logical_reads],
           objlocks.value(''(data[@name="row_count"]/value)[1]'', ''bigint'') AS [row_count],           
           objlocks.value(''(action[@name="database_name"]/value)[1]'', ''varchar(100)'') AS [database_name], 
           objlocks.value(''(action[@name="sql_text"]/value)[1]'', ''varchar(max)'') AS [sql_text],
           objlocks.value(''(data[@name="physical_reads"]/value)[1]'', ''bigint'') AS physical_reads, 
           objlocks.value(''(@timestamp)[1]'', ''datetime'') AS [datas],
           objlocks.value(''(action[@name="session_id"]/value)[1]'', ''int'') AS session_id,
           objlocks.value(''(data[@name="result"]/value)[1]'', ''int'') AS [result],
           RBufXml
    FROM #XmlAsTable heldlocks
         CROSS APPLY RBufXml.nodes(''//event[@name="sql_batch_completed"]'') AS T(objlocks) ) H
 
 ORDER by  duration_sec DESC