14,494 questions
Thanks Erland for the reply.
So enabling only helps troubleshoot and does not improve performance like choosing a better execution plan automatically...and actually heard theres about 3% to 5% performance slower impact?
Downsides of enabling SQL 2016 Query Store on a particular DB?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 45%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
techresearch7777777
1,981
Reputation points
Hello SQL Server 2016 Query Store sounds good.
What are the cons of enabling it, any impacts doing so, and concerns/things to be aware like any further maintenance to plan/monitor?
Thanks in advance.
SQL Server | Other
Accepted answer
-
techresearch7777777 1,981 Reputation points
2021-01-07T01:59:48.377+00:00 -
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator2021-01-07T22:18:56.66+00:00 That is correct to a large extent. Query Store collects data and help you to do performance tuning, but it does not tune your system for you. But there are really nifty feature. All of a sudden a query is running slow despite that it was quick yesterday? As a quick short-term solution, just force yesterday's plan.
You can running queries of Query Store, but there are also GUIs in SSMS you can work with.
There is a feature known as Automatic Plan Forcing which uses the information in Query Store to force a plan if a query appears to have regressed. This feature was introduced in SQL 2017. It is not on by default in the box product, but I believe it is on by default in Azure.
And, the 3-5% is the number that is usually quoted. Although how it strikes to the individual system can vary more.
Sign in to comment -
3 additional answers
Sort by: Most helpful
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
2021-01-06T22:31:41.89+00:00 In most cases, the overhead of Query Store is entirely bearable. But I have heard of people who have had to disable Query Store. I guess one type of system that could suffer is one with lots of ad-hoc queries that are not parameterised.
As for monitoring, it's a good idea to check that Query Store is not set into READ_ONLY because it fills up the space.
Query Store is really great, and I absolutely recommend turning it on, even if you do not intend to perform a performance analysis right now. Ever had the question: "Why was the system so horribly slow between six and seven last night?". Well, with Query Store you may actually be able to answer that question. At least if it was due to a slow query.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 78%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECZ%3C/text%3E%3C/svg%3E)
Cris Zhan-MSFT 6,661 Reputation points2021-01-07T07:02:39.937+00:00 Hi @techresearch7777777 ,
Enabling Query Store usually does not significantly affect performance.
Using the Query Store, you should always apply the latest updates to your SQL Server instances to get all the improvements that Microsoft has implemented for query store. Also follow some best practices.
Check some articles for reference:
https://www.sqlskills.com/blogs/erin/query-store-performance-overhead/
https://www.sqlskills.com/blogs/erin/query-store-best-practices/ -
techresearch7777777 1,981 Reputation points
2021-01-08T21:19:47.933+00:00 Thanks for all the replies, much appreciated.