I love Query Store. I like to think of it as SQL Server’s built-in monitoring tool and I use it a lot to look at query history in terms of run times and IO metrics.

One thing I learned about Query Store recently is how it deals with queries that have been killed.

Setting up the database

For this demo, I am using the 2010 version of the Stack Overflow Database, provided under cc-by-sa 4.0 license from Stack Exchange Data Dump and running in compatibility level 160 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM.

As this demo is working with Query Store, I’ll first enable Query Store on the database:

USE [master];
ALTER DATABASE [StackOverflow2010] SET QUERY_STORE = ON;
ALTER DATABASE [StackOverflow2010] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

The Query

Now we’ve enabled Query Store, let’s run the following query:

SELECT	p.Body,
		u.DisplayName
FROM	dbo.Posts p
		JOIN dbo.Users u
			ON p.OwnerUserId = u.id
WHERE	u.DisplayName LIKE 'a%';

The query is fairly simple, it just gets us some information about the Posts made by users whose name begins with the letter A.

We can look at the Query Store data by using the query below. Note: I am filtering to this specific query by searching for query text containing LIKE ‘a%’ (inception?).

SELECT	t.query_sql_text,
		p.query_plan,
		s.last_duration,
		s.count_executions,
		s.execution_type_desc
FROM	sys.query_store_query_text t
		JOIN sys.query_store_query q
			ON t.query_text_id = q.query_text_id
		JOIN sys.query_store_plan p
			ON p.query_id = q.query_id
		JOIN sys.query_store_runtime_stats s
			ON s.plan_id = p.plan_id
WHERE	t.query_sql_text LIKE '%LIKE ''a%';

We can see one record returned (one query matches the text we asked for) and we can see our query has executed once (see the count_executions column).

We can run the query again several times and our Query Store result will reflect this:

So far, so good. Let’s run our query again but this time, the DBA decides to kill the query while it is running, perhaps it became long-running for some reason and was causing a blocking pile-up or similar.

We kill the query as below – the query is SPID 59 in my case:

KILL 59;

Now, if we execute our Query Store query again:

We can see that the number of executions was not updated, despite the query being executed. So with this in mind, you would think that Query Store only updates this information upon successful completion of a query but you would be wrong…ish.

This time, let’s run the query again but stop the execution from the client. In our case, the client is SSMS and I can stop the query with the red stop button:

At this point, if we execute our Query Store query again:

Now we see that we have another row in the results. The eagle-eyed will note that the difference between the two is the execution_type_desc column and we now have a new row with a value of “Aborted”. Query Store has saved the execution information of the query that our client cancelled.

With this in mind, we know that Query Store will store data for query executions that complete normally or are ended by the client, but not anything killed by an external process. There is a third state: Exception, which occurs when there is a runtime error (think a data truncation error when inserting into a table etc).

I tried to find some evidence of Query Store writing to its system tables after each execution (although data is actually persisted to disk in line with the DATA_FLUSH_INTERVAL_SECONDS setting) in an attempt to prove that it doesn’t do the same thing when a query is KILLed but was unable to find anything in the various Query Store Extended Events or Profiler (probably not unexpected given Profiler is deprecated).

It seems to me that as a KILL is what it says on the tin, the associated Query Store process is not run.

The fact that killed queries are not written to Query Store is worth remembering as I have been in the situation where I have killed a long-running query but then not been able to get the plan from Query Store to do some further investigation into why it was long-running (note to self – grab this information from sp_WhoIsActive BEFORE killing the query!).

Conclusion

We have seen how Query Store persists data in various execution outcomes and observed that when a query is killed, as the name suggests the rug is completely pulled and SQL Server doesn’t persist the data to Query Store. This is something I learned recently after puzzling over why I couldn’t find some data in Query Store. The findings in the post are supported by Microsoft documentation, which states:

execution of killed queries isn’t recorded in Query Store

References / Further Reading

Microsoft – KILL (Transact-SQL)

Microsoft – Query Store hints best practices