Query Store is so cool
Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI.
You can read all about what it does and what you can do with it around the internet. You can be suitably impressed and enchanted by the promise of data that’s persisted between restarts, being able to quickly and easily address plan regression issues, and so forth.
I’m here to bum you out when you’re done with all that.
You can’t use it in tempdb
On tempdb? Whatever.
What? Really? With all the stuff that goes on there? Aside from the many selfish demo reasons I have for wanting it, I know lots of people who make tempdb their default database for user logins so they don’t go messing things up in the real world, or more fragile system databases. I’m also aware of several vendor apps that dump users into tempdb and then use fully qualified names to point them to application data.
I found this all out by accident. I had just nabbed CTP 3 of SQL Server 2016, and was settling in for an evening of staring contests with execution plans and new features.
tragedy struck I got an error message:
ALTER DATABASE tempdb SET QUERY_STORE = ON;
Query Data Store cannot currently be enabled on tempdb
Msg 12438, Level 16, State 1, Line 1
Cannot perform action because Query Store cannot be enabled on system database tempdb.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
And yeah, it’s an official no-go
I’m all disillusioned and stuff, man.
This is going to be frustrating out in the wild for a lot of people. Not just fancy-
pants camo shorts consultants who like to make demos where they treat tempdb like it owes them money. Real people. Who pay real money, for real servers.
Anyway, there’s a Feedback request to get this changed. Vote for it. Or don’t. Just don’t vote more than once, or if you’re dead.