The case for Query Store in tempdb

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.

"Try looking into that place where you dare not look! You'll find me there, staring out at you!"

“Try looking into that place where you dare not look! You’ll find me there, staring out at you!”

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:

And yeah, it’s an official no-go

Oh, but I model and msdb are fair game?

Oh, but model and msdb are fair game?

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, I started a Connect item to get this changed. Vote for it. Or don’t. Just don’t vote more than once, or if you’re dead.

, , ,
Previous Post
My Favorite Moments from the 2015 PASS Summit WIT Lunch
Next Post
Interviewing: How to Test TSQL Writing Skills

15 Comments. Leave new

  • Nice Dune reference. I’m voting for it right now.

    Reply
  • Sean Alexander
    November 3, 2015 12:37 pm

    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.

    Reply
    • 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.

      Reply
      • 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..

        Reply
      • 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!

        Reply
      • 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.

        Reply
  • Voting done! its 108 count as of now:)

    Reply
  • 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.

    Reply
    • Dan – sure, but you can persist it elsewhere, like designate another user database for it.

      Reply
      • Question Brent, how would that other designated db work? Why wouldn’t you just use the other db to begin with?

        Reply
        • 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.

          Reply
          • 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…

    Reply
  • I agree and voted up the item, which has 0 downvotes as of 2011-11-05 14:23:00.00000!

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}