Different query store Views-Stored procedure and Alter commands collective for Query store and its different Configuration Options & How does Query store Works.
Views
The following diagram shows Query Store views and their logical relationships, with compile time information presented as blue entities:
View Descriptions
| View | Description |
|---|---|
| sys.query_store_query_text | Presents unique query texts executed against the database. Comments and spaces before and after the query text are ignored. Comments and spaces inside text are not ignored. Every statement in the batch generates a separate query text entry. |
| sys.query_context_settings | Presents unique combinations of plan affecting settings under which queries are executed. The same query text executed with different plan affecting settings produces separate query entry in the Query Store because context_settings_id is part of the query key. |
| sys.query_store_query | Query entries that are tracked and forced separately in the Query Store. A single query text can produce multiple query entries if it is executed under different context settings or if it is executed outside vs. inside of different Transact-SQL modules (stored procedures, triggers, etc.). |
| sys.query_store_plan | Presents estimated plan for the query with the compile time statistics. Stored plan is equivalent to one that you would get by using SET SHOWPLAN_XML ON. |
| sys.query_store_runtime_stats_interval | Query Store divides time into automatically generated time windows (intervals) and stores aggregated statistics on that interval for every executed plan. The size of the interval is controlled by the configuration option Statistics Collection Interval (in Management Studio) or INTERVAL_LENGTH_MINUTES using ALTER DATABASE SET Options (Transact-SQL). |
| sys.query_store_runtime_stats | Aggregated runtime statistics for executed plans. All captured metrics are expressed in form of 4 statistic functions: Average, Minimum, Maximum, and Standard Deviation. |
Query Store Stored Procedures
==========================1.sp_query_store_force_plan --> Flushes the in-memory portion of the Query Store data to disk.
2.sp_query_store_remove_query --> Removes the query, as well as all associated plans and runtime stats from the query store.
3.sp_query_store_unforce_plan --> Enables unforcing a particular plan for a particular query.
4.sp_query_store_reset_exec_stats --> Clears the runtime stats for a specific query plan from the query store.
5.sp_query_store_remove_plan --> Removes a single plan from the query store.
6.sp_query_store_reset_exec_stats --> Clears the runtime stats for a specific query plan from the query store.
Different Alter commands to Manage Query store
======================================= Use the ALTER DATABASE statement to enable the query storeUSE [master]
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE = ON
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO
If this option is set to lower value than the frequency of flushes, data from memory to disk will occur often which will have negative impact on performance of SQL Server instance. But If the value is increased, more SQL Server Query Store information will be placed in memory of the SQL Server instance before it flushes to disk, which increases a risk of losing that data in case of SQL Server restart/crashes.
Below is the T-SQL code for setting the Data Flush Interval (Minutes) option:
USE [master]
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 1800)
GO
Q:-
The Query Store uses a DATA_FLUCH_INTERVAL_SECONDS option for controlling the memory buffers is flushed to the database.
What is the Default interval for DATA_FLUCH_INTERVAL_SECONDS?
900 Sec
100 Second
700 second
400 second
Ans : 900 Sec
Max Size (MB): Specifies the limit for the data space that Query Store will take inside your database. This is the most important setting that directly affects operation mode of the Query Store.
While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.+
The default value (100 MB) may not be sufficient if your workload generates large number of different queries and plans or if you want to keep query history for a longer period of time. Keep track of current space usage and increase the Max Size (MB) to prevent Query Store from transitioning to read-only mode. Use Management Studio or execute the following script to get the latest information about Query Store size:
USE [master]
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 200)
GO
Statistics Collection Interval: Defines level of granularity for the collected runtime statistic (the default is 1 hour). Consider using lower value if you require finer granularity or less time to detect and mitigate issues but keep in mind that it will directly affect the size of Query Store data. Use SSMS or Transact-SQL to set different value for Statistics Collection Interval:
USE [master]
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 10)
GO
Arbitrary values are not allowed for
INTERVAL_LENGTH_MINUTES. Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.
Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.+
It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.
USE [master]
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, SIZE_BASED_CLEANUP_MODE = ON)
GO
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.+
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.+
Avoid keeping historical data that you do not plan to use. This will reduce changes to read-only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:
USE [master]
GO
ALTER DATABASE [AdventureWorksDW2016_EXT] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 300))
GO
or collective
ALTER DATABASE <database name>
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
/*
--- Configure the
STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store. The default value is 30. For SQL Database Basic edition, default is 7 days.
*/
DATA_FLUSH_INTERVAL_SECONDS = 3000,
/*
Determines the frequency at which data written to the query store is persisted to disk. To optimize for performance, data collected by the query store is asynchronously written to the disk. The frequency at which this asynchronous transfer occurs is configured via
DATA_FLUSH_INTERVAL_SECONDS. The default value is 900 (15 min).
*/
MAX_STORAGE_SIZE_MB = 500,
/*
Configures the maximum size of the query store. If the data in the query store hits the
MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data. The default value is 100 MB. For SQL Database Premium edition, default is 1 GB and for SQL Database Basic edition, default is 10 MB.
*/
INTERVAL_LENGTH_MINUTES = 15,
/*
Determines the time interval at which run time execution statistics data is aggregated into the query store. To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. This fixed time window is configured via
INTERVAL_LENGTH_MINUTES. The default value is 60.
*/
SIZE_BASED_CLEANUP_MODE = AUTO,
/*
Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. Can be AUTO (default) or OFF.
*/
QUERY_CAPTURE_MODE = AUTO,
/*
Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). The default value on SQL Server (from SQL Server 2016 to SQL Server 2017) is ALL, while on Azure SQL Database is AUTO.
*/
MAX_PLANS_PER_QUERY = 1000,
/*
An integer representing the maximum number of plans maintained for each query. The default value is 200.
*/
WAIT_STATS_CAPTURE_MODE = ON -- it only works on sql server 2017
/*
Controls if Query Store captures wait statistics information. Can be OFF or ON (default).
Query the sys.database_query_store_options view to determine the current options of the query store. For more information about the values, see
*/
);
Q.Which Query Store view contains Contains information about Query Store options for this database:-
1.sys.database_query_store_options
2.sys.query_context_settings
3.sys.query_store_plan
4.sys.query_store_query
5.sys.query_store_query_text
6.sys.query_store_runtime_stats
7.sys.query_store_wait_stats
Ans-: 1
1.sys.database_query_store_options
2.sys.query_context_settings
3.sys.query_store_plan
4.sys.query_store_query
5.sys.query_store_query_text
6.sys.query_store_runtime_stats
7.sys.query_store_wait_stats
Ans-: 1
1.The Desired State must be set to 'ON'
2.The Operational Mode must be set to 'READ-WRITE'
3.Nothing further needs to be done.
Ans-: 2.
Which of the Query Store built in reports would be the best to use when ensuring that an upgrade won't result in slow queries?
1.The Top Resource Consuming Queries report
2.The Tracked Queries report
3.The Regressed Queries report
4.The Overall Resource Consumption report
Ans: 3
The following table explains when to use each of the Query Store views:
| SSMS view | Scenario |
|---|---|
| Regressed Queries | Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse). Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved. |
| Overall Resource Consumption | Analyze the total resource consumption for the database for any of the execution metrics. Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database. |
| Top Resource Consuming Queries | Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval. Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption. |
| Queries With Forced Plans | Lists previously forced plans using Query Store. Use this view to quickly access all currently forced plans. |
| Queries With High Variation | Analyze queries with high execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage in the desired time interval. Use this view to identify queries with widely variant performance that can be impacting user experience across your applications. |
| Tracked Queries | Track the execution of the most important queries in real-time. Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable. |
The Query Store uses a DATA_FLUCH_INTERVAL_SECONDS option for controlling the memory buffers is flushed to the database.
What is the Default interval for DATA_FLUCH_INTERVAL_SECONDS?
900 Sec
100 Second
700 second
400 second
What is the Default interval for DATA_FLUCH_INTERVAL_SECONDS?
900 Sec
100 Second
700 second
400 second
Ans :
