SQL Server超时诊断

fablefe

use tempdb　　
go
　　
if exists (select * from sys.objects where name = 'sqlws_xev_attention')
　　
    drop view sqlws_xev_attention
　　
go
　　
create view sqlws_xev_attention as
　　
with xevents (event_data)
　　
as
　　
(
　　
    select event.query('.') as event_data from
　　
        ((select cast (xest.target_data as xml) as target_data
　　
                from sys.dm_xe_sessions as xes
　　
                inner join sys.dm_xe_session_targets as xest on (xes.address = xest.event_session_address)
　　
                where xes.name = 'sqlws_xevents_attention' and xest.target_name = 'ring_buffer') as td
　　
            cross apply target_data.nodes ('//event[@name="attention"]') as x (event))
　　
)
　　
select event_data.value ('(event/@name)[1]', 'varchar(max)') as event_name,
　　
    event_data.value ('(event/@timestamp)[1]', 'datetime') as event_timestamp,
　　
    event_data.value ('(event/data[@name="duration"]/value)[1]', 'bigint') as [duration],
　　
    event_data.value ('(event/data[@name="request_id"]/value)[1]', 'int') as [request_id],
　　
    event_data.value ('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') as action_client_app_name,
　　
    event_data.value ('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') as action_client_hostname,
　　
    event_data.value ('(event/action[@name="database_name"]/value)[1]', 'nvarchar(max)') as action_database_name,
　　
    event_data.value ('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(max)') as action_nt_username,
　　
    event_data.value ('(event/action[@name="session_id"]/value)[1]', 'int') as action_session_id,
　　
    event_data.value ('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as action_sql_text
　　
    from xevents
　　
go
　　
if exists (select * from sys.server_event_sessions where name = 'sqlws_xevents_attention')
　　
    drop event session sqlws_xevents_attention on server
　　
go
　　
create event session sqlws_xevents_attention on server
　　
    add event sqlserver.attention (action (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text) where duration > 5000 )
　　
    add target package0.ring_buffer
　　
    with (event_retention_mode = ALLOW_SINGLE_EVENT_LOSS, memory_partition_mode = NONE)
　　
go
　　
/*
　　
alter event session sqlws_xevents_attention on server state = start
　　
go
　　
--Execute your workload
　　
with xevents (event_name)
　　
as
　　
(
　　
    select event.value ('(@name)[1]', 'varchar(max)') as event_name
　　
        from ((select cast (xest.target_data as xml) as target_data
　　
                from sys.dm_xe_sessions as xes
　　
                inner join sys.dm_xe_session_targets as xest on (xes.address = xest.event_session_address)
　　
                where xes.name = 'sqlws_xevents_attention' and xest.target_name = 'ring_buffer') as td
　　
            cross apply target_data.nodes ('//event[@name="attention"]') as x (event))
　　
)
　　
select event_name, count(*) from xevents
　　
    group by event_name
　　
go
　　
select * from sqlws_xev_attention
　　
go
　　
alter event session sqlws_xevents_attention on server state = stop
　　
go
　　
*/