What are the steps to enable and disable Query Store in SQL Server?

To enable Query Store in SQL Server, you can use either SQL Server Management Studio (SSMS) or Transact-SQL statements. Here are the steps for both methods: Using SQL Server Management Studio (SSMS): Right-click on the database in Object Explorer. Click on Properties. In the Database Properties dialog box, select the Query Store page. In the Operation Mode (Requested) box, select Read Write. Using Transact-SQL statements: You can use the ALTER DATABASE statement to enable the query store for a given database. Here is an example: TSQL ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

When you notice that some queries are taking longer than usual, causing a bit of chaos in the otherwise orderly flow of information. You have to invoke the ancient and powerful command: ALTER DATABASE [database_name] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); All queries are observed and their performance metrics will stored. The Query Store can remember the history of each query, the resources it consumed, and the plans it used to execute. It allows you to identify which queries are causing delays and provides insights into how they could be optimized, and you can see the execution plans and performance data, making informed decisions to keep the data flowing smoothly.

To Turn off / Turn On the query storage in SQL Server environment, We would go to individual database and make changes ( query storage). This would be a tedious process as there may be multiple database in Any single level instances. We can come up with the script that would be implemented To turn off the query storage: SELECT 'USE master;' AS Script UNION ALL SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = OFF;' FROM sys.databases WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource') AND is_query_store_on = 1; To turn on the Query storage : To turn on the Query storage : Set query_storage= on And is_query_store_on = 1; These two changes would make the script run for enabling the query storage on the Database level

To activate the Query Store in SQL Server, connect to the instance via SQL Server Management Studio (SSMS). Select the database, access the properties, and in the “Query Store” tab, set “Operation Mode (Requested)” to “Read Write”. You can also use the following SQL script: sql ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON; ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE); To disable the Query Store, follow the same steps in SSMS and set “Operation Mode (Requested)” to “Off” or use this script : sql ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF; These steps ensure efficient management of the Query Store.

Configure Query Store Settings: You can configure the Query Store settings using the ALTER DATABASE command. Here are some of the settings you can configure: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO ); Remember to replace [YourDatabaseName] with the actual name of your database when executing these commands. Also, make sure to tailor the settings to fit the specific needs of your environment.

With the Query Store, you can quickly: 1. Identify all queries which execution metrics have been degraded, in the period of time of interest (last hour, day, week, etc.). Use Regressed Queries in SQL Server Management Studio to speed up your analysis 2. Identify the top queries consuming the most resources using the Top Resource Consuming Queries feature. 3. Finding waiting queries through Query Wait Statistics.

You can query the Query Store views to analyze the performance data. Some of the key views include: sys.query_store_runtime_stats: Provides runtime execution statistics for queries. sys.query_store_plan: Contains information about query execution plans. sys.query_store_query: Contains information about the queries. For example, to find queries with the highest resource consumption, you could use: SELECT TOP 10 qsrs.query_id, qsrs.avg_cpu_time FROM sys.query_store_runtime_stats AS qsrs JOIN sys.query_store_plan AS qsp ON qsrs.plan_id = qsp.plan_id ORDER BY qsrs.avg_cpu_time DESC;

You can Disbale query store using GUI which very Easy and not more complex. SSMS ===> Database --> Query Store ---Operation Mode --- Off

To disable the Query Store in SQL Server, you can use the following Transact-SQL command: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF; OR ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF (FORCED); The FORCED option stops all related background tasks that are currently running and skips any flushing that would occur to persist data that was in memory to disk. Be aware that using this option may result in the loss of any data that is in memory and has not been flushed to disk.

To clear the data collected by the Query Store in SQL Server, you can use the following Transact-SQL command: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR; This command will remove all the data from the Query Store, including the query text, plan, and any runtime statistics. It’s a way to reset the Query Store if you want to start fresh with no historical data