In my previous blog post I have talked about What is the Query Store and How to setup the Query Store. In this blog post today, I will explain the options that you may need to configure according to the need of your workload, let’s call it the Query Store Best Practice and Configuration.
Query Store Best Practice and Configuration
Best Practice, most probably you all are aware what does this means but let me tell you. Best practice is a method which is generally accepted as standard practice. Best practices are to keep standards or quality or bench-marking. Normally, Best practice is not a rule-of-thumb. But it is a standard method which suits or work best for your environment or organization. And, while it works for your environment, it is not necessory that it will work for other’s environment. For example, in my environment, on any SQL Server, keeping 32 GB RAM is sufficient, but in some other environment it might be the minimum requirement and not sufficient.
I will advise you to keep some bench-marking before concluding what is best practice and configuration to your environment. Let’s talk about the configuration options that we will get here with the Query Store.
Operation Mode:
It has total 3 options – Off, Read Write and Read Only. By default, It is off. Once you enable the query store and make it operation to store the execution plans. When you choose Read Only it will not capture and store the information for execution plans.
Monitoring:
Basically, this option is to configure at what interval the data (execution plans and runtime statistics) will be flush to the disk from the memory.
Data Flush Interval –
This option is to be configured in minutes, by default the value will be 15 minutes. What it means is, at every 15 minutes interval the execution plan related data will be flushed from memory to the disk. If the value is increased there might be a chance of loosing the data in case unexpected restart or server crash. If the threshold is set to lower value, we will be having a good data for analysis because it will be flush to disks more often. You will have to make sure that you choose one of the value from 1,5,10,15,30 and 60 only.
Statistics Collection Interval –
This defines aggrefation interval of query statistics used inside of the query store. Just like the above option and threshold, the lower the value will give us the good data for the analysis.
Query Store Retention:
Here, in this section, there will be configuration options for the retention period for the query store. There are total four options that you can configure, they are:
Max Size (MB) –
This option specifies how much amount of the data can be retained in the query store. By default, the value is 10 MB for Basic and, 1 Giga bytes for premium edition. I am seeing that for me it’s 1 Gigabyte on the developer edition means I can safely say it will be the same on the Enterprise as well.
There is one thing you need to remember here, once the query store hits threshold value i.e. 1 Gigabytes it will switch the operation mode to Read Only. This is because it can’t auto grow, we will need to purge the query store data. This means, it will stop collecting statistics and the execution plan.
Query Store Capture Mode –
This option signifies what type of queries are captured. There are total 3 option for you here.
1) All, what this means is, the execution plan and statistic data of every query executed will be stored in the query store.
2) Auto, here, it will try and ignore the infrequently executed or ad-hoc queries.
3) None, the database engine will capture statistics details for only those queries which are already recorded.
Size Based Cleanup Mode –
As the name suggests, this option will talk about the cleaning up. Once the query store hits the 90% of the max size (mb) value it will perform the cleanup. It will drop the data that are old and less expensive. By default the value is Auto. The other value is Off, when the value is set to off, the operation mode will change to Read Only.
Stale Query Threshold (Days) –
This option defines how long you want to store the data. By default the value is set to 30 days. There is one more option here that you may want to know, the max_plans_per_query. This option can be configured only using T-SQL. 200 is the default value, and I believe it’s the maximum value as well.
ALTER DATABASE DBADB SET QUERY_STORE = ON ( MAX_PLANS_PER_QUERY=200 )
So these are the options that you can configure for the query store. The best practice part, as I said, you will have to choose the value that suits your workload the most. However, here is the LINK where MS has outlined a few options as the best practice, and I will recommend you go through them in case you are using the query store.
If you are still not sure if you would start using the query store. Microsoft has described a few use case scenario for you to go through, here is the LINK. And, here is the LINK where you can read more on how the query store collects data. And, finally, I would like you to know that there may be a slow performance when a database is under heavy workload, and the fix is to update the SQL Server 2016 to CU2/latest available CU or SP.