Query store

Vishu 1,576 Reputation points
2021-06-08T10:42:32.203+00:00

Experts,
Query store is used for capturing a history of executed queries,, execution plans etc. against a specific database. However just to understand what is the impact of enabling it in read-write mode on the general performance. Does it utilize a lot of resources and might impact the overall performance in the long run. For SQL 2017/2019 versions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments No comments

Answer accepted by question author
Erland Sommarskog 137.9K Reputation points MVP Volunteer Moderator
2021-06-08T21:47:39.42+00:00

Generally, it is said that Query Store adds an overhead of 3-4%, and this is perfectly tolerable. Not the least when you take in consideration that it can help you to improve performance by finding slow queries.

However, there are workloads where Query Store can take SQL Server over the brink. The typical case would be an application that submits a lot of short quick queries, and it is even worse if they are unparameterised. They have improved this in SQL 2019 by providing different capture more, and the default mode, AUTO, will ignore these single-use queries.

Query Store is a tremendous asset, and I think you should turn it on, and only turn if off, if you find that it causes problems.

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Cris Zhan-MSFT 6,676 Reputation points
    2021-06-09T02:49:48.737+00:00

    Hi,

    The SQL Server Query Store feature provides you with insight on query plan choice and performance. Usually it does not generate too much performance overhead.

    Check the doc--Best practices with Query Store
    https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15

    Always apply the latest CU to your SQL Server 2017&2019 instance to get any improvements in Query Store or other aspects.

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.