How To Capture Query Timeouts With Extended Events

 

We can capture Query Timeouts by using SQL Server Extended Events Session.

Right click on Management-> Extendend Event-> Sessions on SSMS and select new session wizard.

On the first page, click next.

On the second page, we give a name to Session and select “Dont use template” and click next.

On the page that opens, we write “attention” in the “Event Library” section. And by clicking on the attention below, we add “attention” to the Selected Events by the arrow on the right.

Click Next to proceed.

On the opened page, we select the values of queries that experince timeout problem. For example login name of query, text of query etc.

You can find descriptions of the fields that you can select in the Description section.

The fields I chose for my queries that experience timeout problem are as follows:

  • client_hostname
  • database_name
  • is_system
  • sql_text
  • username

We are filtering as in the screen below. We want to capture queries that are connected with “denemeUser” and who are experiencing a timeout problem.

Click Next, Next and Finish to create the Extended Events Session.

Right-click on Timeout Session under Management-> Extended Events-> Sessions on SSMS and click Start. And then right click again to watch live data. All queries that are connected with “denemeUser” and who are experiencing a timeout problem will now appear in here.

If you want to filter in the result set, you can do this by right-clicking in the upper left corner as follows by clicking “Filter by this Value”.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *