How To Enable Query Store In SQL Server

Recently, I got a requirement from one of my clients to enable a query store for one of our SQL databases. In this article, I will walk you through the quick steps we followed to enable query store in sql server 2019.

How To Enable Query Store In SQL Server

Let us discuss all the approaches individually.

Approach-1: Using SQL Server Management Studio (SSMS)

To enable query store in an SQL server, follow the steps below.

1. Log in to SQL Server Management Studio and connect to your SQL server instance.

2. Right-click on the database name and select the Properties option, as shown in the screenshot below.

3. Click on the Query Store option from the left side and then choose Read Write for the Operation Mode (Requested) option. Finally, click on the OK button to save the changes, as shown in the screenshot below.

Check out How to View SQL Server Error Logs

Approach-2: Using SQL Query

We can also execute the SQL query below to enable the query to be stored in the SQL server. Here Test is the name of my SQL Server database.

ALTER DATABASE Test
SET QUERY_STORE = ON
    (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = ALL,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 30
    );

After executing the above SQL script, I got the expected output, shown in the screenshot below.

Now, to cross-check if it is done successfully, we can execute the SQL select statement below.

SELECT actual_state_desc, desired_state_desc
FROM sys.database_query_store_options;

After executing the above query, we got the status Read_Write, which is enabled, as highlighted in the screenshot below.

Video Tutorial

Conclusion

Enabling Query Store in SQL Server is crucial for performance monitoring and query optimization. As discussed in this article, you can easily do this by using the SQL Server Management studio design approach and SQL query. You can choose the approach that best suits your requirements.

You may also like following the articles below.

• How To Enable Filestream In SQL Server
• How to Export Data from SQL Server to Excel
• How to Find Stored Procedure in SQL Server