The short answer:
-
The majority of workloads won’t see an impact on system performance
- Will there be an increase in resource use (CPU, memory)? Yes.
- Is there a “magic number” to use to figure out Query Store performance and the increase in resource use? No, it will depend on the type of workload. Keep reading.
-
An impact on system performance can be seen with ad-hoc workloads (think Entity Framework, NHibernate), but I still think it’s worth enabling. With an ad-hoc workload there are additional factors to consider when using Query Store.
-
You should be running the latest version CU for SQL Server 2017 and latest CU for SQL Server 2016 SP2 to get all performance-related improvements Microsoft has implemented specific to Query Store
Definitely read the long answer. There are also settings to reduce the load that Query Store puts on a system, and being up to date is critical.