Sql-server – Blocking caused by Query Store. Unable to clear or disable

blockingquery-storesql serversql-server-2016

I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the SPID b/c it isn't a user process. According to SP_WHO2, the command is "Query Store ASYN". I've tried purging the data and disabling the query store via Script and through the UI. Nothing seems to work and it just spins and then starts to cause more blocking. Is anyone else having this issue? Can anyone please help me figure out how to disable the query store successfully? SP_WhoIsActive @show_System_SPIDS = 1 results below (query store results only)

UPDATE – This is now caused TempDB drive to fill up. Going to try a reboot in a few hours and see if that resolves the issue. Will keep you posted.

Thanks,
Nate

Best Answer

Based on your explanation, I believe this is related to KB4461562, and was resolved in the latest Cumulative Update 5.

FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017

Related Solutions

SQL Server – Pros and Cons of Read Committed Snapshot Isolation vs Read Committed

By default, SQL Server writers block readers and vice versa if Read Committed Snapshot Isolation is not enabled. If the application is not written exclusively for SQL Server, it may cause lots of extra locks (SQL Server can also escalate locks) when database is used more or less extensively.
Enabling Read Committed Snapshot Isolation solves this issue and improves concurrency.

On the other hand, it adds load on tempdb (never a problem from my experience at least for SQL Server 2008R2 and higher) and may potentially cause some consistency issues for applications written for SQL Server only. Note, it has nothing to do with dirty reads.

SQL Server 2016 Query Store – Problems and Solutions

This was a known bug with Query Store on Standard and Express.

Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup will produce a dump file. Cleanup activation period depends on the workload frequency, but it is no longer than 15 min.

The work around is to turn off cleanup policies and manually clear query store data.

to clear all query store data

ALTER DATABASE <database name> SET QUERY_STORE CLEAR

to selectively clear data

sp_query_store_reset_exec_stats

sp_query_store_remove_plan

sp_query_store_remove_query

The issue was first fixed in Cumulative Update 1 for SQL Server 2016:

Query Store automatic data cleanup fails on editions other than Enterprise and Developer edition of SQL Server 2016 (KB 3178297)

Best Answer

Related Solutions

SQL Server – Pros and Cons of Read Committed Snapshot Isolation vs Read Committed

SQL Server 2016 Query Store – Problems and Solutions

Related Question