StartTime and EndTime for the Attention Extended Event

Question

The documentation says that the "Attention" extended event class has StartTime and EndTime as part of it.

But I can't seem to figure out how to get at it or record it...

Here is what I am using to create the event session:

CREATE EVENT SESSION [TimeoutMonitor] ON SERVER
    ADD EVENT sqlserver.attention(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,
          sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.server_principal_name,
          sqlserver.sql_text,sqlserver.username))
    -- Save to our file.  Max size of 500 megabites and 3 rollover files (so it can take up 1.5 GB total)
    ADD TARGET package0.event_file(SET filename=N'd:\TimeoutMonitoring\TimeoutMonitor.xel', max_file_size=500, max_rollover_files=3)

But if I try to add sqlserver.start_time (or any variant like StartTime) I get an error.

And if I try to query as if it is just automatically recorded, I also get an error.

Is there a way to get start time and end time for Attention Events?

Answer 1

That's because the documentation is for a Profiler and not Extended Events

This can be confirmed by going to the root of the documentation subtree to the page SQL Server Event Class Reference

SQL Server Profiler lets you record events as they occur in an instance of the Microsoft SQL Server Database Engine. The recorded events are instances of the event classes in the trace definition.

Opening the profiler - you can see that the columns match.

But profiler is deprecated and using the Extended Events is the way to go.

Extended Events solution

The attention event you're using is not giving much information, just that attention has happened and how long it took (but not how long the query took).

A better option is to use events sql_batch_completed and rpc_completed - both have a field called result, which you can filter for the value Abort.

Both events have a duration field as well as cpu_time and other useful information right out of the box. You can also add other actions for even more information.

Answer 2

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ReadClientTimeout2]
(
      -- Add the parameters for the function here
      @xel VARCHAR(500),
      @xem VARCHAR(500)
)
RETURNS TABLE
AS
RETURN
(
WITH qry AS (
SELECT
  theNodes.event_data.value('(/event/@timestamp)[1]','datetime2(3)') AS TS1,
theNodes.event_data.value('(action[@name="database_name"]/value)[1]','varchar(50)')
           AS database_name,
theNodes.event_data.value('(action[@name="client_hostname"]/value)[1]','varchar(50)')
           AS client_hostname,
theNodes.event_data.value('(action[@name="client_app_name"]/value)[1]','varchar(50)')
           AS client_app_name,
theNodes.event_data.value('(data[@name="duration"]/value)[1]','bigint') AS duration,
theNodes.event_data.value('(action[@name="sql_text"]/value)[1]','varchar(4000)') AS sql_text,
theNodes.event_data.value('(action[@name="user_name"]/value)[1]','varchar(50)') AS user_name,
theNodes.event_data.value('(action[@name="is_system"]/value)[1]','varchar(50)') AS is_system,
theNodes.event_data.value('(action[@name="nt_user_name"]/value)[1]','varchar(50)')
           AS nt_user_name,
theNodes.event_data.value('(action[@name="server_principal_name"]/value)[1]','varchar(50)')
           AS server_principal_name
FROM
      (SELECT CONVERT(XML,event_data) event_data
            FROM
       sys.fn_xe_file_target_read_file(@xel, @xem, NULL, NULL)) AS theData
       --sys.fn_xe_file_target_read_file('F:\MSSQL\Logs\execution_timeout*.xel',NULL, NULL, NULL)) as theData
CROSS APPLY theData.event_data.nodes('//event') theNodes(event_data)
       )
SELECT DATEADD(MI, DATEDIFF(MI, GETUTCDATE(), GETDATE()), TS1) AS TS1,
database_name,client_hostname,client_app_name,
            duration,sql_text,user_name,is_system,
            nt_user_name,server_principal_name
             FROM qry
 )
GO