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?
Best Answer
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.