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!