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.