What is the Query Store in SQL Server 2016? – Rakesh asked me. And, the discussion continued for half an hour. I have explained to Rakesh the feature, the basics. And, how it can benefit the customers from the performance point of view. Because the Query store will be helping a lot to pick the efficient execution plan in case of performance issue reported, it will be the loved feature to both – the DBAs and the Developers. I will be writing a series of blog post on this feature. This is the part one of the series.
Store – It is the quantity or supply of something kept for use as needed, the dictionary says. The other word, Query – a question, especially one expressing doubt or requesting information. To set the context, let’s understand the word with the example. When Rakesh asked me about this feature, it is some information that he was needed, and he gets the information from myself or some articles over the internet can be considered the Store.
What is the Query Store in SQL Server 2016?
Basically, Query Store will store the multiple execution plans of the same query. And, You and I can analyze different execution plans for the same query, choose the efficient one and force query to USE PLAN. Now, You can quickly recall a procedure cache. Yes, procedure cache also stores the execution plans but it stores the latest execution plan only. You and I also need to remember that these plans stored in the Procedure Cache will be wiped out due to memory pressure which is not the case with the Query Store. There are a few advantages of using the Query Store, let’s see what they are:
- Quickly find and fix a plan performance
- Identify top resource intensive queries (CPU, Memory, IO or Execution Time)
- Auditing history of the query plan
- Find and fix the plan performance using a USE PLAN hint
- Analyze resource utilization
In the next blog post, I will explain how to configure the Query Store in detail, with the demo. See you next Tuesday. In the meantime, you may want to browse through the blog posts I have written under HowItWorks and Configuration category.
