Monitoring SQL Server For Query Timeouts With Extended Events

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.

Eventful

---

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 ON SERVER.

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.

The 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 rpc_completed or sp_statement_completed which can additionally filter to an object_name to get you to a specific procedure as well.

Stressful

---

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.

Wonderful

---

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!

Going Further

---

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. You can also get a quick, low cost health check with no phone time required.