No tags yet.
Use Cases for Query Store
Why should I use Query Store?
Query store can be used for a number of things. For example:
Find out what was happening on my server last night?
Identify and fix queries suffering from parameter sniffing or plan regression
Testing (ie - when upgrading to a new version of SQL server, or development)
What happened on my server last night?
Query store is great for a historical view into what your server was doing. It's often referred to as "a flight data recorder".
There were so many times where I was asked "what happened to so and so's process last night? why did it run so long etc..."
Query store would have saved me a ton of time to find out what was going on and ideas on how to correct it.
Top resource consuming queries with timeline.
Identify and Fix queries suffering from Parameter sniffing or Plan regression:
(Also see my article on parameter sniffing here.)
There are times when the Query Optimizer may choose a different plan due to certain values for parameters. And in rare cases, this new plan is not optimal for most inputs and thus gives us degraded performance.
In the past this was difficult to identify and remedy. Query store has empowered us as DBAs and developers.
Identify multiple plans and then you can force one or the other.
Testing
There are actually a couple of scenarios in regards to testing. One would be for an upgrade or migration project. Another could be for a development project.
With a migration, you can do a a dry run and run a workload on a database with query store enabled. Look at the top consuming queries and see if you want to tune them or force a plan etc...
As with the above paragraph, the same approach can be used when working on a development project. You can identify queries, parameter sniffing ahead of time and either tune the queries or force the plan before the go live date.