on April 18, 2018
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.