The Performance Impacts Of Query Store

Erin Stellato explains the performance impacts of enabling Query Store in various types of environments:

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.

Related Posts

Query Store: Forcing Parallel Plans

Grant Fritchey shows us that you can force a parallel plan using Query Store: I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven’t a clue. The trick I think is going to be in setting up the test. Let’s try it out. Reliably Getting a […]

Read More

Finding Missing Index Hints in Query Store

Grant Fritchey shows us another place where we can find missing index hints: A couple of notes on the query. I cast the query_plan as xml so that I can use the XQuery to pull out the information. It is possible that the plan might be so large that you get an error because of […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930