Trouver les requêtes qui n’ont pas abouti

Avec le Query Store, il est possible de trouver les requêtes qui n’ont pas abouti, à cause d’un timeout applicatif par exemple (en général 30 secondes).

SELECT 
       ROUND(rs.avg_duration /1000000,0) AS avg_duration
     , rs.max_duration /1000000 AS max_duration
     , rs.count_executions
     , qt.query_sql_text
     , q.query_id
     , qt.query_text_id
     , p.plan_id
	 , rs.first_execution_time
     , rs.last_execution_time
     , rs.execution_type_desc
     , qpx.query_plan_xml
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
	OUTER APPLY (SELECT TRY_CONVERT(XML, p.query_plan) AS query_plan_xml) AS qpx
    JOIN sys.query_store_runtime_stats AS rs
        ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -6, GETUTCDATE())
	AND rs.execution_type =3 -- Aborted
ORDER BY rs.max_duration DESC
       , rs.count_executions DESC;

SELECT

ROUND(rs.avg_duration /1000000,0) AS avg_duration

, rs.max_duration /1000000 AS max_duration

, rs.count_executions

, qt.query_sql_text

, q.query_id

, qt.query_text_id

, p.plan_id

, rs.first_execution_time

, rs.last_execution_time

, rs.execution_type_desc

, qpx.query_plan_xml

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

OUTER APPLY (SELECT TRY_CONVERT(XML, p.query_plan) AS query_plan_xml) AS qpx

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

WHERE rs.last_execution_time > DATEADD(HOUR, -6, GETUTCDATE())

AND rs.execution_type =3 -- Aborted

ORDER BY rs.max_duration DESC

, rs.count_executions DESC;