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)

SP_WhoIsActive 1 SP_WhoIsActive 2

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

  • You are running SET QUERY_STORE = OFF but it is turned on again? I would trace and see if some other process is doing that. – Jacob H Sep 14 '18 at 13:48
  • I would think that statement would be immediate. However, it just spins while executing it and then starts to cause more blocking. So I end up canceling the query. – Nate Sep 14 '18 at 13:58
  • Any offline index rebuild in progress ? Also, find the lead blocker and try to kill it. We need more details - install sp_whoisactive. – Kin Shah Sep 14 '18 at 14:16
  • There are no index rebuilds going on at the moment. I have SP_WhoIsActive installed, but that doesn't show these process SPIDS. Both the blocking SPID and the blocked SPID are Query Store commands. – Nate Sep 14 '18 at 14:24
  • 1
    run with @show_system_spids = 1. – Kin Shah Sep 14 '18 at 14:31

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.