Identifying Timeouts in SQL Server with Extended Events

In this video we looks at how to capture timeouts in SQL Server using Extended Events.

Firstly we look at what a timeout is in SQL Server and how to trigger one. Most of us would think that it's a query that has simply exceeded it's 'timeout setting' and stopped running, however SQL isn't aware of what that setting is, so just views Timeouts as aborted transactions. The great thing about this is that we can replicate this by cancelling the query. To show how we capture this in Extended Events I add a WAITFOR DELAY within a query and ensure implicit transactions are turned off and run a simple query against the database and kill it before the WAITFOR has been hit - easy!

The settings of how to setup this XE is shown, so you can set this up within your home SSMS if you wanted and have a play around before introducing it in a live environment. The great thing is that Extended Events is very lightweight so shouldn't have a detrimental effect on performance - bonus!

Finally we run through how to group and view this data, which is great as the same method can be applied to any XE.

I have several other tutorials on Extended Events as part of my mini course which I link to in this video so take a look. As always let me know if you have any questions - and give the video a like!

Content

00:00 Intro
00:38 Understanding SQL Timeouts
01:43 Extended Events Timeouts
02:21 XE Settings - Creating your event
03:16 Capturing Timeouts
05:20 Viewing your data