Export Query Store tables separately from the database tables - by Peter_Tran

Status : 

 


17
0
Sign in
to vote
ID 2620017 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 4/22/2016 6:41:26 AM
Access Restriction Public

Description

Scenario: 

I'd like to get the Query Store tables from my customer's database for analysis. Due to security (SOC2) or EU regulation, I'm now allowed to get the customer's data.

Sign in to post a comment.
Posted by Lonny Niederstadt on 6/13/2016 at 9:24 AM
I'll second what Erin mentioned - good value in query store portability among instances for testing purposes.
What Brent mentions(privacy-protected data within query plans & query text) is a data privacy concern for me, too.
Important to note that this is not a new privacy concern. The long-standing practice of "clone database" with DDL & stats only presents this conundrum whenever a column typically considered private information (eg SSN, etc) is in the query predicate and there are relevant stats.
It can take forever to scrub or recreate stats so they can be shared outside of an NDA or business associate agreement.
Posted by Nicolas SOUQUET on 5/13/2016 at 1:27 AM
Brent's answer leads us to another tool that would be great : a query normalizer.
IT would basically remove hard-coded values (as in Brent's example) and variables values (as it can show up in sp_executesql for example).
This in turns eases finding out offending queries based on their execution frequency.
Posted by Erin Stellato on 5/11/2016 at 12:36 PM
I don't know that I need the data to exist in a separate filegroup (that's a "nice to have"), it's more being able to export the Query Store data in some manner. And not just to get it from a client, but to use for comparison between copies of the database different environments (e.g. production vs. test). Export also assumes I have a way to import it back in to another copy of the database :)
Posted by Peter_Tran on 4/27/2016 at 2:00 PM
I can't modify the proposed solution, but remove the "additional request regarding the security request".
Posted by Peter_Tran on 4/22/2016 at 10:00 AM
That's a good point...can't win them all. There's nothing that can be done with literals in the predicate. I guess it wouldn't really matter whether we remove the bind values, since this is still a security hole with the literals. We'll need to make the customer aware of this if they're providing us the Query Store tables.

In most cases (95+%) of our customers don't care. They often give us the entire database for one reason or another and we have NDAs for all them, but a handful of customers are pretty strict and will not give us anything.

Thanks for your feedback.
Posted by Brent Ozar on 4/22/2016 at 6:59 AM
Queries and query plans can also include customer data. For example, if your customer has:

SELECT * FROM dbo.Customers WHERE LastName = 'Tran' AND FirstName = 'Peter' AND HasDeadlyIllness = 1

Then you're going to get that identifying query in Query Store. Are you sure you're allowed to get that out?