No tags yet.
It's amazing that we never had a built in feature that logged historical record of queries. Sure we had the plan cache, but that was cleared with a service restart or reboot. How many times have you walked into an environment and they would say the server was slow yesterday, or at 2:00am last night, what happened?
Well, it's tough to pin point if you didn't create some means of logging (hint: try logging the output of sp_whoisactive periodically!) or a third party tool.
What is it?
As of SQL Server 2016, Microsoft has introduced a feature called "Query Store". Query store provides a persistent means of viewing the history of executed queries along with a bunch of statistics as well as execution plans.
Is this feature turned on by default? No, but it should be! Until then, feel free to come up with a custom solution to enable it by default (think, modelDB).
Why do I need it?
You can identify expensive queries based on CPU, I/O, Memory.
Historical reference for Queries (so you know if any over night process was running slow! or if it was executed a number of times)
Possibly resolve parameter sniffing issues by forcing execution plans.
How do I set it up?
Right click on your database, go to properties and select query store.
I typically set the operation mode to Read/Write mode as it will collect query plans and runtime statistics.
Statistics collection interval will determine how often query information will be collected. it's up to you to figure out a balance between disk space and granularity.
Max size determines the maximum size of the query store. Bear in mind that this feature doesn't autogrow like a database. upon reaching the maximum size it will switch to what is known as read only mode and no longer collect plans and statistics.