Have you setup the Query Store yet?

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.

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.