question

Vishu-2318 avatar image
0 Votes"
Vishu-2318 asked Criszhan-msft answered

Query store

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.