Click on the banner to learn about and purchase my database training on Azure

SQL Server – How to identify timeout or broken connections using Extended Events (XE) or SQL Profiler (Trace)

Views: 359
Reading Time: 10 minutes

Hello everybody!
The theme of today's post is timeout, which consists of a timeout (usually measured in seconds) in which an operation will wait until it forcibly terminates execution, if this timeout is reached.

The purpose of this post is to show how to identify timeout or broken connections using Extended Events (XE) or SQL Profiler (Trace) and facilitate the analysis of this type of problem that is so common in everyday life.

After identifying the root cause of the timeout, the solution to solve this is to try to optimize the queries through Performance Tuning techniques to make the execution time be reduced. Depending on the process, if it really is something that involves a lot of processing and it really takes time, it's worth increasing the timeout time for that particular process, but that's a subject for my series of Performance Tuning in SQL Server.

In SQL Server, we have several types of timeouts, which I would like to highlight:

  • Remote Query Timeout (sp_configure), which defines the maximum time that remote queries (using linked server) can run (default time: 600 seconds)
  • lock_timeout (SET command), which is the time that the client executing a SQL command will wait for a lock to be released to continue executing that command (default time: -1 = infinity = will wait indefinitely)
  • Command Timeout is a property of database connection drivers, such as ADO.NET, JDBC and others, that allows you to set a maximum time limit that an SQL statement can execute against the database. This value can be dynamically changed in the application and can have different timeouts depending on the context. (default time: 30 seconds)
  • Connection Timeout is a property of connection string which allows you to set a maximum time limit the connection driver will wait to establish a connection to the database (default time: 30 seconds)
Important
It is very important to make clear that the lock_timeout, Command Timeout and Connection Timeout timeouts they are limits imposed by the client/connection driver and not the database.

When a timeout occurs, it will generate an exception in the application, but from the database point of view, there was no error or problem in the execution (or connection), precisely because the timeout does not occur in the database, that is, it is not the bank that is interrupting the SQL statement, but the application.

Notes
If you want to know more about SQL Server SET commands, go to the article SQL Server SET Commands.

I also recommend reading my article Timeout when running Queries via Linked Server on SQL Server, if you are experiencing any problem related to this.

Also, let's not forget the timeout of the development tool (IDE) that the DBA or developer uses to access the database.

SQL Server Management Studio (SSMS), for example, has a property called “Execution time-out”, which defines how long SSMS should wait to execute a query (internally this is the Command Timeout property of the connection driver).

The default value of this property in SSMS is 0 (infinite), that is, it has no time limit.

How to generate a timeout using SQLCMD

In order to be able to create some test logs and demonstrate how to identify timeouts in the database, I will force timeouts to occur in the database.

For this, I will use the SQLCMD utility and the property -t 1 (query timeout of 1 second), and I will use the command WAITFOR DELAY '00:00:05′, which waits for 5 seconds, that is, whenever I execute this command, I will generate an event timeout.

To know a little more about the SQLCMD utility, I suggest reading the article SQLCMD - The SQL Server command line utility.

Execution Result:

Generated timeouts.

How to identify database timeouts using Extended Events (XE)

To identify timeouts in the database, I've seen many people use the query execution time to define whether there was a timeout or not. As the default is 30 seconds, one assumes that if the query took exactly 30 seconds, it is because a timeout occurred. However, this is not always true, because, as I mentioned at the beginning of the post, for each context or screen of the system, the developer can define different timeout times.

One of the ways to be able to identify whether a timeout really occurred is using Extended Events (XE), analyzing the attention event class, available since SQL Server 2012, which indicates that an event that requires attention has occurred, such as cancellations, client interrupt requests, and lost client connections. Cancel operations can also be seen as part of implementing driver data access timeouts (timeouts)

I'll share the script ready to implement the monitoring and also prints of the Extended Events interface, in case you want to create it yourself, via screen.

Script to monitor SQL Server timeout using Extended Events (XE)

Interface to monitor timeout in SQL Server using Extended Events (XE)
Click to view screenshots

Screenshot 1 – Open XE Wizard from SSMS Object Explorer

Screenshot 2 – Home screen

Screenshot 3 – Define the Extended Event name and check the checkbox to enable the event at the end of the configuration

Screenshot 4 – Marking that I don't want to use any template

Screenshot 5 – Select the “attention” event and add it to the list of selected events

Screenshot 6 – Confirm selection and proceed to next screen

Screenshot 7 – Selecting the return fields you want the event to return

Screenshot 8 – Moving forward without applying filters

Screenshot 9 – Define the location to save the .xel file, maximum size and rollover of files

Screenshot 10 – Final screen and operations summary

Script to read data written by Extended Event (XE)
Click to view the script

Example of returned data:

How to identify database timeouts using SQL Profiler (Trace)

Another way to identify timeouts in the database is to use SQL Profiler (Trace), especially if you are using a version prior to 2012, which does not support the Extended Events attention event.

To achieve this goal with SQL Profiler, we will monitor the RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events and filter only the results that meet the Error = 2 filter.

Important
Even applying this filter, you should closely monitor if your server's workload will increase a lot after activating this trace, because everything that is executed will be analyzed by this trace.

Whenever possible, try to use Extended Events (XE) instead of Profiler (Trace), especially for this situation.

Script to monitor timeout on SQL Server using SQL Profiler (Trace)

Interface to monitor timeout in SQL Server using SQL Profiler (Trace)
Click to view screenshots

Screenshot 1 – Opening SQL Profiler

Screenshot 2 – Connecting to database

Screenshot 3 – Select the Tuning template and click on the “Event Selection” tab

Screenshot 4 – Check the “Show all columns” checkbox, check the “Error” column (and the others you want to return) and click the “Column Filters” button

Screenshot 5 – Apply a filter on the Error column and use the measure Error = 2. Check the checkbox “Exclude rows that do not contain values”

Screenshot 6 – Click the Run button to start the trace on the server

Screenshot 7 – Analyze the results found

Screenshot 8 – Results collected and already stored in a history table

Excerpt from a live where the Marcio Junior demonstrates how to identify timeout in SQL Server

And that's it folks!
A big hug and even more.