How to enable Query store in sql server

Query store is a new powerful feature introduced in SQL 2016 which stores the query plan, statistics and query execution for current and past

queries.

You can enable it from SSMS ( 2016 and above) by clicking on the database and then on properties tab on the left side you can find

query store. In the operation mode select ‘Read Write’ and click on ‘OK’. This will enable the Query Store.

To turn query store off on SSMS change the operation mode to ‘OFF’

 

Query Store feature in sq server

Query store can be enabled by using below sql query.

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

For turning the query store off using query use the below sql query.

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = OFF;

Note : Query store cannot be enabled on master or tempdb database.

Watch this Video on how to enable Query store in sql server.

 

 6,484 total views,  4 views today

admin

Share
Published by
admin

Recent Posts

What is slow changing dimension?

The attribute value in Dimension table are going to change slowly over time and they…

5 years ago

How to Change SQL Server Compatibility level

  Changing Database compatibility level will be required when you upgrade your database from one…

6 years ago

View Compatibility level of Database

To know what is compatibility Level check this post link. You can view the compatibility…

6 years ago

SQL Server Compatibility Level

SQL Server Compatibility level will be used to decide how SQL will use it features.…

6 years ago

View in SQL server

A view is a virtual table.  View will not store any data physically. The data…

6 years ago

SSIS :The package failed to load due to error 0xC0011008

Below is the error which I was getting today while deploying the SSIS pacakge for…

6 years ago