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.

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.
    – Sylvia
    Nov 29, 2023 at 20:34

1 Answer 1

2

If you want to track query timeouts, I 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)

You have it described here:

https://adbsql.blogspot.com/2019/11/tracking-query-timeouts-with-extended.html

Note: that's my own blog post.

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.

3
  • I think 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. Extended Events And Stored Procedure Parameter Values Nov 29, 2023 at 21:20
  • Thanks for this, I'm trying it out.
    – Sylvia
    Dec 11, 2023 at 23:32
  • Hey, you're awesome! Thank you so much, this works well and is exactly what I needed. The full statement, with parameters, shows up in the Statement field. Previously I had never even heard of the Result = 2 filter.
    – Sylvia
    Dec 13, 2023 at 21:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.