QTip: Getting timeouts and exceptions using Azure SQL DB Query Store

Requirements:

Azure SQL Database

SQL Server Management Studio (SSMS)

Tables testcodes and testinvoice used for this demo

1-Configure Query Store to collect data and save as soon as possible to be able to review it

* Data flush interval is the time to send information to disk

** Statistics collection interval is the time range to collect data , query store is not collecting one record by execution is collecting statistics of each execution and you can get max, min, avg but not excecution by excecution

*** Query store capture mode ALL will collect information of all executions query store will not make any kind of filter

2-Create table testcodes

CREATE TABLE [dbo].[testcodes](
	[Code] [char](5) NULL,
	[Description] [nchar](10) NULL
) ON [PRIMARY]
GO

3-Create table testinvoice

CREATE TABLE [dbo].[testinvoice](
	[code] [char](5) NULL,
	[pieces] [int] NULL
) ON [PRIMARY]
GO

4-Add some codes to be used

5-Add some records to invoice

Exception ...

6-Run query to get information from invoices and get description from codes

select *,description=(select testcodes.Code from testcodes where testcodes.code=invoice.code)  from testinvoice invoice

Is possible to see execution without error

7-Now add in codes a second code BBB to force exception

8-Run query from point 6 again

Result is an exception

9-Run query to see queries with exceptions or timeouts starting 2 days ago (declare @datestart as datetime = dateadd(D,-2,getdate());)

declare @datestart as datetime = dateadd(D,-2,getdate());
declare @datefinish as datetime = getdate();

/* if you want to set to specific time */
--set @datestart = '2025-04-09 00:00:00';
--set @datefinish = '2025-04-09 23:59:59';

select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, 
rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount,
rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop,
p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish 
and (rs.execution_type=3 or rs.execution_type=4)  -- 3 timeout, 4 error
--and  qt.query_sql_text like '%actual%'
--and q.query_hash=0x009C458D20394C37
--and p.plan_id=12
ORDER BY rs.last_execution_time DESC

10-In query there are some commented lines that you can use to add more filters or modify them

To see all records comment line 20

declare @datestart as datetime = dateadd(D,-2,getdate());
declare @datefinish as datetime = getdate();

/* if you want to set to specific time */
--set @datestart = '2025-04-09 00:00:00';
--set @datefinish = '2025-04-09 23:59:59';

select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, 
rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount,
rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop,
p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish 
--and (rs.execution_type=3 or rs.execution_type=4)  -- 3 timeout, 4 error
--and  qt.query_sql_text like '%actual%'
--and q.query_hash=0x009C458D20394C37
--and p.plan_id=12
ORDER BY rs.last_execution_time DESC

Now you can see all records

11-Reproduce error several times within 1 minute to see value in execution count (number of executions inside of statistics collection interval)

Timeout ...

12-Configure command timeout different to 0 for this demo use 10 (seconds) in parameters previous to connect

Second option

12-Use query below that will run 1000 times and at some point will fail

INSERT INTO  [dbo].[testinvoice] SELECT * FROM [dbo].[testinvoice] 
GO 1000

13-Run query from point 9 to see data in query store

Now you can reproduce and get data about all excecutions , exceptions and timeouts good luck!