Below is the Query use to to check aborted query details in Azure SQL Database

Query

SELECT TOP 50 
 q.query_hash, 
 qt.query_sql_text,
 cast(p.query_plan as xml) query_plan,
 rs.execution_type,
 rs.execution_type_desc,
 rs.count_executions,
 rs.last_execution_time,
 (rs.avg_duration / 1000000.0) avg_duration_seconds, 
 (rs.min_duration / 1000000.0) min_duration_seconds, 
 (rs.max_duration / 1000000.0) max_duration_seconds, 
 (rs.last_duration / 1000000.0) last_duration_seconds
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p on q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs on p.plan_id = rs.plan_id
WHERE rs.execution_type = 3 
--and query_sql_text like '%abc%' 
--and q.query_hash =0xB8795E6BD1D4FECB
ORDER BY rs.last_execution_time DESC

Explanation of Each Part

This query retrieves details of the top 50 aborted queries from the Query Store in an Azure SQL Database. The Query Store is a feature that helps track the performance of queries over time. It captures query execution statistics, execution plans, and query texts to help troubleshoot performance issues.

SELECT TOP 50

• This selects the top 50 rows based on the criteria provided. You can adjust this number to retrieve more or fewer rows.

Selected Columns

1. q.query_hash: A unique identifier representing a specific query across multiple executions. It is used to track the same query text over time.
2. qt.query_sql_text: The actual SQL text of the query that was executed. It helps you see the exact query that was run.
3. cast(p.query_plan as xml) query_plan: The execution plan of the query, cast as XML. It shows how the SQL Server engine executed or attempted to execute the query.
4. rs.execution_type: Indicates the type of execution. A value of 3 represents “aborted” executions, meaning the query was canceled or failed before completing.
5. rs.execution_type_desc: A textual description of the execution_type (e.g., “Aborted”).
6. rs.count_executions: The total number of times this query was executed.
7. rs.last_execution_time: The last time this query was executed.
8. (rs.avg_duration / 1000000.0) avg_duration_seconds: The average duration of the query execution in seconds, converted from microseconds.
9. (rs.min_duration / 1000000.0) min_duration_seconds: The shortest execution time for the query, converted to seconds.
10. (rs.max_duration / 1000000.0) max_duration_seconds: The longest execution time for the query, converted to seconds.
11. (rs.last_duration / 1000000.0) last_duration_seconds: The duration of the most recent execution of the query, converted to seconds.

FROM Clause: Joining Tables

The query retrieves data from multiple sys.query_store system tables:

• sys.query_store_query AS q: Stores information about queries captured by the Query Store, including their query_id.
• sys.query_store_plan AS p: Contains the execution plans associated with the queries, linked by query_id.
• sys.query_store_query_text AS qt: Contains the text of the queries executed, linked by query_text_id.
• sys.query_store_runtime_stats AS rs: Stores runtime statistics for the queries, including execution duration and count, linked by plan_id.

The query joins these tables using the appropriate columns (query_id, query_text_id, and plan_id) to fetch comprehensive details about each aborted query.

WHERE Clause

• WHERE rs.execution_type = 3: Filters the results to include only queries that were aborted (execution type 3 indicates aborted queries).

Optional Filters (Commented)

• --and query_sql_text like '%abc%': You can uncomment this line to filter for queries that contain specific text (e.g., containing “abc”).
• --and q.query_hash =0xB8795E6BD1D4FECB: You can use this to filter for a specific query using its query_hash.

ORDER BY Clause

• ORDER BY rs.last_execution_time DESC: Orders the results by the most recent aborted queries first.

What the Query Does

The query retrieves details about the top 50 most recently aborted queries from the Azure SQL Database Query Store, including information such as query text, execution plan, execution counts, and execution times. This information is valuable for troubleshooting issues with queries that were terminated before completion, helping to identify potential performance problems or other issues within the database.