Class QueryStoreSchema.DatabaseQueryStoreOption
- Namespace
- LinqToDB.Tools.DataProvider.SqlServer.Schemas
- Assembly
- linq2db.Tools.dll
sys.database_query_store_options (Transact-SQL)
Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics
Returns the Query Store options for this database.
Applies to: SQL Server (SQL Server 2016 (13.x) and later), SQL Database.
[Table(Schema = "sys", Name = "database_query_store_options", IsView = true)]
public class QueryStoreSchema.DatabaseQueryStoreOption- Inheritance
-
QueryStoreSchema.DatabaseQueryStoreOption
- Extension Methods
Properties
ActualState
Indicates the operation mode of Query Store. In addition to list of desired states required by the user, actual state can be an error state.
0 = OFF
1 = READ_ONLY
2 = READ_WRITE
3 = ERROR
[Column("actual_state")]
[NotNull]
public short ActualState { get; set; }Property Value
ActualStateAdditionalInfo
Currently unused. May be implemented in the future.
[Column("actual_state_additional_info")]
[Nullable]
public string? ActualStateAdditionalInfo { get; set; }Property Value
ActualStateDesc
Textual description of the actual operation mode of Query Store.
OFF
READ_ONLY
READ_WRITE
ERROR
There are situations when actual state is different from the desired state:
- If the database is set to read-only mode or if Query Store size exceeds its configured quota, Query Store may operate in read-only mode even if read-write was specified by the user.
- In extreme scenarios Query Store can enter an ERROR state because of internal errors. Starting with SQL Server 2017 (14.x), if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. If running sp_query_store_consistency_check doesn't work, or if you are using SQL Server 2016 (13.x), you will need to clear the data by running ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR ALL;
[Column("actual_state_desc")]
[Nullable]
public string? ActualStateDesc { get; set; }Property Value
CapturePolicyExecutionCount
Query Capture Mode CUSTOM policy option. Defines the number of times a query is executed over the evaluation period. The default is 30.
Applies to: SQL Server 2019 (15.x) and later.
[Column("capture_policy_execution_count")]
[Nullable]
public int? CapturePolicyExecutionCount { get; set; }Property Value
- int?
CapturePolicyStaleThresholdHours
Query Capture Mode CUSTOM policy option. Defines the evaluation interval period to determine if a query should be captured. The default is 24 hours.
Applies to: SQL Server 2019 (15.x) and later.
[Column("capture_policy_stale_threshold_hours")]
[Nullable]
public int? CapturePolicyStaleThresholdHours { get; set; }Property Value
- int?
CapturePolicyTotalCompileCpuTimeMs
Query Capture Mode CUSTOM policy option. Defines total elapsed compile CPU time used by a query over the evaluation period. The default is 1000.
Applies to: SQL Server 2019 (15.x) and later.
[Column("capture_policy_total_compile_cpu_time_ms")]
[Nullable]
public long? CapturePolicyTotalCompileCpuTimeMs { get; set; }Property Value
- long?
CapturePolicyTotalExecutionCpuTimeMs
Query Capture Mode CUSTOM policy option. Defines total elapsed execution CPU time used by a query over the evaluation period. The default is 100.
Applies to: SQL Server 2019 (15.x) and later.
[Column("capture_policy_total_execution_cpu_time_ms")]
[Nullable]
public long? CapturePolicyTotalExecutionCpuTimeMs { get; set; }Property Value
- long?
CurrentStorageSizeMB
Size of Query Store on disk in megabytes.
[Column("current_storage_size_mb")]
[Nullable]
public long? CurrentStorageSizeMB { get; set; }Property Value
- long?
DesiredState
Indicates the desired operation mode of Query Store, explicitly set by user.
0 = OFF
1 = READ_ONLY
2 = READ_WRITE
[Column("desired_state")]
[NotNull]
public short DesiredState { get; set; }Property Value
DesiredStateDesc
Textual description of the desired operation mode of Query Store:
OFF
READ_ONLY
READ_WRITE
[Column("desired_state_desc")]
[Nullable]
public string? DesiredStateDesc { get; set; }Property Value
FlushIntervalSeconds
The period for regular flushing of Query Store data to disk in seconds. Default value is 900 (15 min).
Change by using the ALTER DATABASE <database> SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = <interval>) statement.
[Column("flush_interval_seconds")]
[Nullable]
public long? FlushIntervalSeconds { get; set; }Property Value
- long?
IntervalLengthMinutes
The statistics aggregation interval in minutes. Arbitrary values are not allowed. Use one of the following: 1, 5, 10, 15, 30, 60, and 1440 minutes. The default value is 60 minutes.
[Column("interval_length_minutes")]
[Nullable]
public long? IntervalLengthMinutes { get; set; }Property Value
- long?
MaxPlansPerQuery
Limits the maximum number of stored plans. Default value is 200. If the maximum value is reached, Query Store stops capturing new plans for that query. Setting to 0 removes the limitation with regards to the number of captured plans.
Change by using the ALTER DATABASE<database> SET QUERY_STORE (MAX_PLANS_PER_QUERY = <n> statement.
[Column("max_plans_per_query")]
[Nullable]
public long? MaxPlansPerQuery { get; set; }Property Value
- long?
MaxStorageSizeMB
Maximum disk size for the Query Store in megabytes (MB). Default value is 100 MB up to SQL Server 2017 (14.x), and 1 GB starting with SQL Server 2019 (15.x) .
For SQL Database Premium edition, default is 1 GB and for SQL Database Basic edition, default is 10 MB.
Change by using the ALTER DATABASE <database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <size>) statement.
[Column("max_storage_size_mb")]
[Nullable]
public long? MaxStorageSizeMB { get; set; }Property Value
- long?
QueryCaptureMode
The currently active query capture mode:
1 = ALL - all queries are captured. This is the default configuration value for SQL Server (SQL Server 2016 (13.x) and later).
2 = AUTO - capture relevant queries based on execution count and resource consumption. This is the default configuration value for SQL Database.
3 = NONE - stop capturing new queries. Query Store will continue to collect compile and runtime statistics for queries that were captured already. Use this configuration cautiously since you may miss capturing important queries.
4 = CUSTOM - Allows additional control over the query capture policy using the QUERY_CAPTURE_POLICY options.
Applies to: SQL Server 2019 (15.x) and later.
[Column("query_capture_mode")]
[NotNull]
public short QueryCaptureMode { get; set; }Property Value
QueryCaptureModeDesc
Textual description of the actual capture mode of Query Store:
ALL (default for SQL Server 2016 (13.x))
AUTO (default for SQL Database)
NONE
CUSTOM
[Column("query_capture_mode_desc")]
[Nullable]
public string? QueryCaptureModeDesc { get; set; }Property Value
ReadonlyReason
When the desired_state_desc is READ_WRITE and the actual_state_desc is READ_ONLY, readonly_reason returns a bit map to indicate why the Query Store is in readonly mode.
1 - database is in read-only mode
2 - database is in single-user mode
4 - database is in emergency mode
8 - database is secondary replica (applies to Always On and Azure SQL Database geo-replication). This value can be effectively observed only on readable secondary replicas
65536 - the Query Store has reached the size limit set by the MAX_STORAGE_SIZE_MB option. For more information about this option, see ALTER DATABASE SET options (Transact-SQL).
131072 - The number of different statements in Query Store has reached the internal memory limit. Consider removing queries that you do not need or upgrading to a higher service tier to enable transferring Query Store to read-write mode.
262144 - Size of in-memory items waiting to be persisted on disk has reached the internal memory limit. Query Store will be in read-only mode temporarily until the in-memory items are persisted on disk.
524288 - Database has reached disk size limit. Query Store is part of user database, so if there is no more available space for a database, that means that Query Store cannot grow further anymore.
To switch the Query Store operations mode back to read-write, see Verify Query Store is Collecting Query Data Continuously section of Best Practice with the Query Store.
[Column("readonly_reason")]
[Nullable]
public int? ReadonlyReason { get; set; }Property Value
- int?
SizeBasedCleanupMode
Controls whether cleanup will be automatically activated when total amount of data gets close to maximum size:
0 = OFF - size-based cleanup won't be automatically activated.
1 = AUTO - size-based cleanup will be automatically activated when size on disk reaches 90 percent of max_storage_size_mb. This is the default configuration value.
Size-based cleanup removes the least expensive and oldest queries first. It stops when approximately 80 percent of max_storage_size_mb is reached.
[Column("size_based_cleanup_mode")]
[NotNull]
public short SizeBasedCleanupMode { get; set; }Property Value
SizeBasedCleanupModeDesc
Textual description of the actual size-based cleanup mode of Query Store:
OFF
AUTO (default)
[Column("size_based_cleanup_mode_desc")]
[Nullable]
public string? SizeBasedCleanupModeDesc { get; set; }Property Value
StaleQueryThresholdDays
Number of days that the information for a query is kept in the Query Store. Default value is 30. Set to 0 to disable the retention policy.
For SQL Database Basic edition, default is 7 days.
Change by using the ALTER DATABASE <database> SET QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <value> ) ) statement.
[Column("stale_query_threshold_days")]
[Nullable]
public long? StaleQueryThresholdDays { get; set; }Property Value
- long?
WaitStatsCaptureMode
Controls whether Query Store performs capture of wait statistics:
0 = OFF
1 = ON
Applies to: SQL Server 2017 (14.x) and later.
[Column("wait_stats_capture_mode")]
[NotNull]
public short WaitStatsCaptureMode { get; set; }Property Value
WaitStatsCaptureModeDesc
Textual description of the actual wait statistics capture mode:
OFF
ON (default)
Applies to: SQL Server 2017 (14.x) and later.
[Column("wait_stats_capture_mode_desc")]
[Nullable]
public string? WaitStatsCaptureModeDesc { get; set; }