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.
That’s when tragedy struck I got an error message:
|
1 2 3 4 5 6 7 8 |
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.
15 Comments. Leave new
Nice Dune reference. I’m voting for it right now.
Why would we encourage people messing around in tempdb like it’s a solution to all their problems?
Good on Microsoft for having a sense of reason.
After all, persisting anything removes the “Temp” in Temporary.
Sean – because some shops set users’ default database to be TempDB. That way, if they accidentally create objects (man, if I had $1 for every time I’ve seen a deployment happen in master), then they’re not in such bad shape.
Brent.. so true… got to love cleaning up master on a Sat morning when someone goofed an changed master by mistake. Oh BTW I have a rule, no releases to Prod on Friday’s… 😉
Eric how are the walking dead going to vote? BTW fear is the mindkiller, I will face my fear..
I don’t know , if you have user deployments in master you have other issues in your environment that need to be addressed as opposed to setting default database to tempdb. I have seen environments where user objects were created in master, and funny that, how the security in said environment was as open as a lady of the night in the seedier parts of Amsterdam. Masking an issue is not resolving a potential catastrophe.
Saying that I have seen environments where user deployments of code has caused an entire market marker to go bankrupt in a single day. They called it a trading glitch!
Corne – there are always other issues that need to be addressed. Welcome to IT.
I wouldn’t like to have things dropped without warning when the instance restarts even if they shouldn’t be there. Maybe we should create a dummy database with no space for the users default database.
Voting done! its 108 count as of now:)
Possibly one reason for the restriction is that tempdb is re-created every time SQL Server is restarted? Therefore, you cannot persist data in tempdb.
Dan – sure, but you can persist it elsewhere, like designate another user database for it.
Question Brent, how would that other designated db work? Why wouldn’t you just use the other db to begin with?
Okay, I answered my own question for part of it. I am guessing while you setup, you can opt to have the data store in another db for any db. I also realized after googling that this is for Azure only? I was getting excited about using this since I cannot implement the data store on all my databases centrally. Thanks.
Verena – keep reading. 😀 The feature is also available in SQL Server 2016, which is what we’re talking about in the post. Keep up that reading work! 😉
What a great idea, I have never thought to change the default database to tempdb… I do routinely delete tables and procs out of master because of developers with sa privs they usually catch what they do, but they never clean up after themselves…
I agree and voted up the item, which has 0 downvotes as of 2011-11-05 14:23:00.00000!