Earlier tonight, I sat down to install Reporting Services on my laptop. I haven’t played with SSRS 2017 yet, so I figured I’d poke around some. Much to my surprise, the configuration failed when creating the ReportServer database, reporting a deadlock. When I looked at the database instance, I found the ReportServer database there, but with no objects it in, and the ReportServerTempDB database missing.
A quick project isn’t going to be very quick
I tried the configuration a couple of times just to make sure it wasn’t a one-off problem. I installed the latest Cumulative Update (CU). I made sure nothing else was connected to the instance. I rebooted my machine. I restarted services. I banged my head against the wall. I asked a friend if I was insane or stupid. After confirming that I was both, my friend Aaron Bertrand (blog|twitter) confirmed it wasn’t a problem for him.
I discovered I could reproduce the problem simply by running the same simple
Along the way, I used Extended Events to grab the deadlock graph. The deadlock graph just made me even more confused, as it showed that the CREATE DATABASE was just deadlocking on itself:
That simply didn’t make any sense
How can database creation deadlock with itself? And if it deadlocked, how was the database created anyway? And why the heck does collation matter?
I took a closer look at the deadlock graph, and could see that the object locks involved in the deadlock were on sys.sysschobj. This is the internal system table that backs the data in the sys.objects system view. This means that after the database is created. SQL Server is doing something inside the database, modifying objects. What would do that?
I opened up the deadlock XML directly, and that’s when I saw this:
That transaction name stood out to me because it includes “QDS”… (Not DQS: That’s the abbreviation for Data Quality Services) QDS is the abbreviation used for Query Store (I think the D stands for “Database”? “Data”?).
I had enabled Query Store on my model database after reading Erin Stellato’s (blog|twitter) article on Query Store configurations. I liked the idea of enabling Query Store on model, and having it be automatically configured on every database I created. This would ensure I had consistent Query Store configuration across databases, and seemed like a good idea at the time. That works great… but apparently creates a problem when changing collations.
It’s a query store bug
The title of this article probably tipped you off, but I realized this must be a bug with query store. I disabled query store on model, and retried my CREATE DATABASE statement that has been failing all night. And it worked.
It turns out that if you enable Query Store on model, and then attempt to create a database with non-default collation, the CREATE DATABASE will fail with a deadlock while Query Store does it’s initial configuration. This leads me to assume that even though the database is created, Query Store would not be fully healthy. I’ve created a bug report on UserVoice, requesting that I be able to enable Query Store in model, and subsequently create a database with a non-default collation. If this is something you’d like to do, please upvote that item on UserVoice.