In the previous blog post, I have discussed the Query Store (QS) explaining what it is? In this blog post, today, I will explain how it can be configured. It is pretty easy when it comes to setting up this very feature of SQL Server 2016. This feature can be configured using the UI using SQL Server Management Studio (SSMS) or we can use the T-SQL via SSMS.
Setting up Query Store using UI using SSMS
Step 1: Right-click on the database you want to set-up QS
Step 2: From the left side of pane select QS
Step 3: In the right side of the pane, in the General Section change the Operation Mode to Read Write
Setting up Query Store using T-SQL using SSMS
It is really easy and pretty simple, you just need to execute the query I have mentioned below and it will enable QS for the database DBADB that I have used in the example. This is the dummy database that I use for testing, it’s pretty small in size but it’s okay to use for an explanation like this. I am going to download and use Stack Overflow database if you haven’t heard so far. Read about it here. Read the documentation and use it for the demo purpose, it’s a great work Brent did like he does always.
ALTER DATABASE DBADB SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE )
In the following blog post on this very topic, I will explain a few more features for Query Store like Data Flush Interval, Statistics Collection Interval, Max Size for retention, Query Store Capture Mode, Size-Based Cleanup and Stale Query Threshold.
There are many more such interesting troubleshooting tips that you may want to explore, please browse through the Troubleshooting and Configuration category of this blog space. And, do not forget to let me know how do you like this blog via a comment section.
