How to quantify or locate command timeouts in Azure SQL

Posted on

Question :

I am trying to if confirm query timeout in Azure.

Our frontdoor is reporting a 503 response at 30 Seconds response, as its the SQL server default value, I am trying to confirm if these are indeed query command timeouts.

I’ve searched for “Timeout” in the Logs section of the portal monitoring (not sure this how they would be represented)

Is there an method to locate query timeouts in Azure?

Answers or links with a decent resource would be highly appreciated as my searches are not proving fruitful at the moment

Answer :

Is there an method to locate query timeouts in Azure?

Here are some links:

Monitor Azure SQL Database using Azure SQL Analytics (Preview)
https://docs.microsoft.com/en-us/azure/azure-monitor/insights/azure-sql

Monitoring performance by using the Query Store
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

Extended Events in Azure SQL Database https://docs.microsoft.com/en-us/azure/sql-database/sql-database-xevent-db-diff-from-svr

You can try using Extended Events as shown below:

CREATE EVENT SESSION ssEventoTimeout
 ON DATABASE
 ADD EVENT sqlserver.sql_batch_completed (
 ACTION  (sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
 WHERE ([result] <> (0)))
 ADD TARGET package0.asynchronous_file_target(
 SET filename='https://azureblobcontainer.blob.core.windows.net/xe-container/DemoPersistedTimeout.xel')

Taken from a comment:

This type of information isn’t usually collected by SQL Server (or Azure SQL Database). If you don’t have some sort of monitoring software then it will be hard to confirm. I would like to point you to using Extended Events, docs.microsoft.com/en-us/archive/blogs/docast/… – Jonathan Fite

This is a screenshot of Azure SQL Analytics.

You set up a Log Analytics Workspace and attach your databases to it. Note it takes a few minutes for them to show up but you can then see timeouts.

It’s still in preview and definitely a bit klunky, but I’m hoping to get some useful information from it.

enter image description here

One thing to note is if you drill down to a query that timed out you’ll see a query hash which you can locate in your Azure SQL Query Store with:

select sys.query_store_plan.query_id, query_hash, plan_id, 
sys.query_store_query.initial_compile_start_time, sys.query_store_plan.initial_compile_start_time, 
query_sql_text 
from sys.query_store_plan 
join sys.query_store_query on sys.query_store_plan.query_id = sys.query_store_query.query_id
join sys.query_store_query_text on sys.query_store_query.query_text_id = sys.query_store_query_text.query_text_id
where 
query_hash = 0x7FF00FC6FDBC40C5
order by plan_id desc

(note: no quotes around query hash!)

Oddly the query hash it’s telling me has timed out isn’t showing as timed out in query store. So I guess that means the timeout wasn’t due to an inefficient query plan, but general resources – or maybe a table lock – something like that.

Leave a Reply

Your email address will not be published.