1

I have an extended event session set up to track Attention events, to monitor procedures that timed out. This is the create script:

CREATE EVENT SESSION [ApplicationTimeout3] ON SERVER 
ADD EVENT sqlserver.attention(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
ADD TARGET package0.event_file(SET filename=N'D:\ExtendedEvents\ApplicationTimeout3.xel',max_file_size=(100),max_rollover_files=(5))
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=OFF,STARTUP_STATE=ON)
GO

The problem is that in the timeouts that I get, I do NOT get the sql_text. (Nor do I get tsql_frame or tsql_stack). It's not returned when I do the "Watch Live Data".

I do SEE the timeouts, they occur at the same time as the ones I see when I query against sys.query_store_runtime_stats (with execution_type_desc = 'Aborted'). However, I get a limited set of fields back, and they're not the ones I need.

Also incidentally, there are many MORE events logged via the Extended Event than what I see when I query sys.query_store_runtime_stats. I have no idea what they are.

Any clues as to how I could actually get the sql that's being executed? I can get the stored procedure name by querying the query store tables. What I'd like to get is the parameters that the stored procedure is called with.

Improve this question
1
  • I'm looking for the full sql text of the stored procedure that's being executed. I can find the stored procedures via query store. I need the parameters. Commented Nov 29, 2023 at 20:34

2 Answers 2

Reset to default
3

If you want to track query timeouts, I have found this Extended Event to be more useful:

CREATE EVENT SESSION [QueryTimeouts] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.database_name,sqlserver.username)
    WHERE ([result]=(2))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name,sqlserver.username)
    WHERE ([result]=(2)))
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'G:\QueryTimeouts.xel', max_file_size = 5, max_rollover_files = 2)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS)

I described here.

I used it few years ago, so I'm not 100% sure, but I think you should get the queries with runtime parameters. I also had problems with the attention event as you describe. It seemed to produce bogus entries. That's why I switched to rpc_completed and sql_batch_completed.

Only the statement attribute captures parameters. You may need an event other than rpc_completed to capture stored procedure executions, depending on how they're called. See Extended Events And Stored Procedure Parameter Values by Grant Fritchey.

Improve this answer
0
1

For investigating attention events you don't necessarily need any tracing but can look in query store where execution_type_desc = 'Aborted'.

Some more details of this type of approach and some caveats are in the article Find query execution timeouts with Query Store.

SQL Server 2022 also has the query_abort extended event. This does show the input buffer of the aborted session as well as the task call stack.

The Youtube video Why does a query fail when there is no error? | SQL Server 2022 Hidden Gems | Data Exposed provides more details on this as well as showing how the task call stack can be used to get some additional insights into what exactly the aborted query was up to.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.