SQL Server : If a query is cancelled, where to get data on it?

by Jean   Last Updated July 24, 2015 16:02 PM

Suppose am tracking a query using sys.dm_exec_query_stats, if the query ends fine, then we can find the relevant statistics in sys.dm_exec_query_stats. But if a query is cancelled, the statistics of that query (i.e. how long was it executing before being cancelled by the user, its I/O usage, and so on) is not there in sys.dm_exec_query_stats.

Is there any other place to get this info?

Tags : sql-server dmv

Answers 2

You'd have to be polling sys.dm_exec_requests rather frequently, or using trace / extended events to target queries that ended with abort. With the disclaimer that I work for SQL Sentry, Performance Advisor - among many other things - tracks all aborted queries for you, as long as they exceed the Top SQL threshold (5 seconds by default, but that is configurable). It's possible other monitoring tools might do something similar.

Aaron Bertrand
Aaron Bertrand
July 24, 2015 14:36 PM

Starting with SQL Server 2016 you can track this using Query Store. sys.query_store_runtime_stats highlights the canceled query stats explicitly as execution_type 'Aborted'.

Remus Rusanu
Remus Rusanu
July 24, 2015 20:01 PM

Related Questions

creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM