SQLSaturday Cleveland 2017

Cleveland peeps – we are a week away from SQLSaturday Cleveland, are you registered?!  There’s still time if you’re not, AND there is still time to register for one of the fantastic pre-cons we’re hosting this year.  Your options:

Pre-con cost is $175, which is a deal compared to what it would cost if you attended the same pre-con at the PASS Summit (add in travel costs, and it’s a steal).  Then consider that the group will be much smaller than what it would be at Summit, so you’ll have plenty of opportunities to talk directly to Adam or Ginger to ask questions.  It’s a no-brainer…so much so that I’m attending Adam’s session.  I spend a fair bit of time tuning but there is always more to learn and different perspectives are great for improving troubleshooting skills.

So talk to your manager, re-work your schedule for next week, and sign up!  If you’ll be there, stop by and say hi, or say hi on Saturday where I’ll be at the registration desk (warning: I don’t do mornings well so forgive me if I’m in a daze!) and then I’ll be presenting my new Query Store session at 2:45 PM.  I hope to see you there, and happy Fri-yay!

p.s. Don’t forget to print your SpeedPass!  🙂

T-SQL Tuesday #86: Storing and Accessing the Query Store Data

T-SQL Tuesday

T-SQL Tuesday

The crew over at Brent Ozar Unlimited is hosting this month’s T-SQL Tuesday, and the request was to find an interesting Connect item (bug fix or enhancement request) and write about it.  I’ve been knee-deep in Query Store, and there are two Connect items that immediately came to mind.

One of the primary uses of Query Store is finding query regressions – that’s where I start with every presentation and that’s how it’s been pitched by Microsoft from the beginning.  And typically we talk about troubleshooting production issues that are happening right now, but Query Store is also great for finding regressions and problems before you upgrade or make changes.  If you run Query Store in production, and have some code changes you want to test, you can restore a backup of production, make changes, then run your queries and use Query Store to do comparisons.  But when I start talking about this, I can see people start to think about their own testing process, and they start asking questions….

Where does the Query Store data live?  In the user database.

Can I change the location of the Query Store data? No, it resides in the PRIMARY filegroup.

Can I export the Query Store data? Technically yes, but it’s not a supported method.

What if I have sensitive data in Query Store that can’t exist in a non-Production environment? Just like any restore of a database with sensitive data, you’re going to have to obfuscate it.

Two of these requests have Connect items, which I’m listing below.  The first allows you to change the location of Query Store data to reside somewhere in the user database besides the PRIMARY filegroup:

Option to store query store data in a filegroup other than PRIMARY

The other request is related to exporting that data, which is technically possible now, but it’s not a supported method so it’s not something I really want to implement in a client environment.  I’ve had many people describe their process for testing which includes restoring database nightly.  If they’re using Query Store as part of testing, that data is lost every night by the restore.

Export Query Store tables separately from the database tables

Find time to up-vote the Connect items you think are most valuable!  Haven’t used Connect before?  You just need to create a Microsoft account (it’s free) and then you can vote.  If you use SQL Server on a daily basis, I expect you’d like provide input into the development of the product you’re supporting.  Have fun!