10

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

Improve this question
12
  • 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
    Commented Sep 14, 2018 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
    Commented Sep 14, 2018 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
    Commented Sep 14, 2018 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
    Commented Sep 14, 2018 at 14:24
  • 1
    run with @show_system_spids = 1.
    – Kin Shah
    Commented Sep 14, 2018 at 14:31

2 Answers 2

Reset to default
4

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

Improve this answer
1

I am having the same problem with SQL Server 2016 SP2 CU12, it seems that the FIX included in Cumulative Update 5 does not solve it.

I have seen that there is also KB4511715:

FIX: Filled transaction log causes outages when you run Query Store in SQL Server 2016 and 2017

And that is fixed in SQL Server 2016 SP2 CU8, but considering that my version is SP2 CU12, it seems that it does not work as expected either.

At the moment we have scheduled a restart of the Instance and an update to the latest CU to see if it is solved.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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