Ready Or Not
Most applications have a grace period that they’ll let queries run for before they time out. One thing that I notice people really hate is when that happens, because sometimes the effects are pretty rough.
You might have to roll back some long running modification.
Even if you have Accelerated Database Recovery enabled so that the back roll is instant, you may have have 10-30 seconds of blocking.
Or just like, unhappy users because they can’t get access to the information they want.
Monitoring for those timeouts is pretty straight forward with Extended Events.
Here’s the event definition I used to do this. You can tweak it, and if you’re using Azure SQL DB, you’ll have to use
ON DATABASE instead of
CREATE EVENT SESSION timeouts ON SERVER ADD EVENT sqlserver.sql_batch_completed ( SET collect_batch_text = 1 ACTION ( sqlserver.database_name, sqlserver.sql_text ) WHERE result = 'Abort' ) ADD TARGET package0.event_file ( SET filename = N'timeouts' ) WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF ); GO ALTER EVENT SESSION timeouts ON SERVER STATE = START; GO
There are a ton of other things you can add under ACTION to identify users running the queries, etc., but this is good enough to get us going.
sql_batch_completed event is good for capturing “ad hoc” query timeouts, like you might see from Entity Framework queries that flew off the rails for some strange reason 🤔
If your problem is with stored procedures, you might want to use
sp_statement_completed which can additionally filter to an object_name to get you to a specific procedure as well.
To do this, I’m going to use the lovely and talented SQL Query Stress utility, maintained by ErikEJ (b|t).
Why? Because the query timeout setting in SSMS are sort of a nightmare. In SQL Query Stress, it’s pretty simple.
And here’s the stored procedure I’m going to use:
CREATE OR ALTER PROCEDURE dbo.time_out_magazine AS BEGIN WAITFOR DELAY '00:00:06.000'; END; GO
Why? Because I’m lazy, and I don’t feel like writing a query that runs for 6 seconds right now.
After a few seconds, data starts showing up in our Extended Event Session Viewer For SSMS Pro Azure Premium For Business 720.
But anyway, if you find yourself hitting query timeouts, and you want a way to capture which ones are having problems, this is one way to do that.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.
- SQL Server Extended Event Duration Filtering Can Make Troubleshooting Frustrating
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: Capturing Which Queries Are Compiling With sp_HumanEvents
- SQL Server Community Tools: Capturing Query Performance Problems With sp_HumanEvents
One thought on “Monitoring SQL Server For Query Timeouts With Extended Events”
Comments are closed.