Posted on

Query Store Cleanup Can be Part of a Blocking Chain

Forgetfulness can lead to learning something new. This is a bit of a nightmare when it happens in production, but a treat when it happens in an isolated test system– and that’s how I learned this.

I left a bit of blocking open on my test VM, and forgot about it.

I was using the BabbyNames sample database. In one session, I had run:

begin tran
    alter table ref.FirstName add foo int null

I left this session sleeping, its open transaction holding a schema modification lock against the ref.FirstName table.

In another session, I ran:

select * from ref.FirstName

I did my testing with this setup, then went back to editing video, and forgot about it.

I forgot to unblock it.

Later, I came back and wanted to measure something in Query Store

This is an isolated test system, so I went to clean out Query Store as a reset. I didn’t need any of the old information in there, so I ran:

ALTER DATABASE BabbyNames SET QUERY_STORE CLEAR ALL;
GO

I was surprised when I didn’t see this complete very quickly, as it normally does.

I checked for blocking in sp_WhoIsActive, and found that my cleanup was blocked

I used Adam Machanic’s free sp_WhoIsActive procedure to check what was going on. Here is the blocking chain:

Clearing Query Store created two sessions

Session 40 shows no sql_text, but it appears when I run the QUERY_STORE CLEAN all command, and disappears when I cancel it. It also shows not “sql_command” if I run sp_WhoIsActive with @get_outer_command=1, and it shows no lock information if I use @get_locks=1.

It’s clearly getting locks, because it’s blocking its little friend, session 74, but it’s just not showing what it’s doing.

Meanwhile, session 74 is waiting on a lock on sys.sysschobjs

Although we can’t see the locks directly on session 40, I can see that session 74 (who is blocked by session 40, who is in turn locked by the alter table) is waiting for a shared key lock on the cost index on the sys.sysschobjs system table.

Sys.sysschobjs is documented, it “Exists in every database. Each row represents an object in the database.”

If I connect to the Dedicated Admin connection, I can query the sys.syssschobjs table – and I can read it if I allow dirty reads (seeing the uncommitted data):

USE BabbyNames
select * from sys.sysschobjs (NOLOCK)
where name = 'FirstName';
GO

There is a modified date on this table (column name modified), which was updated around when I started the modification on the table.

I don’t think this is a bad thing – I’m writing this post just to document it

One cool thing in Query Store is that it has some knowledge of the objects in a database. It will tell you if a query is part of a procedure, for example.

That’s a good thing, but it is probably one of the reasons that Query Store reads from the same system tables that may be locked if we modify the schema of objects.

Hopefully, most folks don’t:

  • Have long running transactions that modify objects in production
  • Clear out Query Store data often in production (it’s useful information, and the basis for some cool features)

If you are unfortunate enough to have BOTH of these patterns, you may run into blocking.

This could also occur if you are trying to clear out Query Store when an offline index rebuild is running.

2 thoughts on “Query Store Cleanup Can be Part of a Blocking Chain

  1. […] Kendra Little shows that you can block Query Store cleanup: […]

  2. Thanks, I hadn’t thought about problems occurring because of this scenario. There’s also a case where writes to Query Store can occur in the context of an implicit or explicit read transaction that gets left open, resulting in log records being written out for the transaction and preventing the log from clearing until the transaction ends:

    https://dba.stackexchange.com/questions/191825/why-would-a-select-query-cause-writes/191829#191829

    The last 3 places I’ve worked have had at least one app that left implicit transactions open indefinitely (this is the default behavior in SQLAlchemy for read transactions as far as I can tell), so I wouldn’t be surprised if this becomes a fairly common problem on databases that have Query Store enabled. As with the scenario you wrote about, I don’t think this is necessarily a design flaw in Query Store, just something that’s useful to be aware of.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.