Long running queries often causes client timeout in our applications. One good solution to attack this problem is to identify the timeouts and optimize the queries causing the timeouts.

Client timeouts or connection interruptions are identified as Attention events. We can capture the attention events using an Extended Events session.

The script to create the XE session will be the following:

You need to customize the path for the XE files in the filename attribute of the target.

After activating the session and let it running, we need to query the information from the XE session. This kind of query uses a lot of XML, so the best solution is to create a function to turn this query easier.

The script for the function will be this:

We need to test and see our new session capturing a timeout. In SSMS, go to File->New -> Database Engine Query.

In the “Connect to database engine” window, click in Options->Connection Properties and change the value of “Execution timeout” to 5 seconds. Fill the information in the Connection tab and click ok to open a new query window with five seconds as execution timeout.

Now you will receive a timeout each time you run the following instruction:

We need to query the function to see the information captured about the timeout:

We can use client_app_name, database_name and other fields to identify the timeout events. Notice that sql_text contains the query that caused the timeout and needs to be optimized.