Getting SQL Server Context Settings

Frank Gill has a script to find current Query Store context settings:

The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):

USE YourQueryStoreDatabase;
SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions
FROM sys.query_context_settings;

The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.

Read on to get a script which breaks the bitmask field into human-readable results.

Related Posts

Query Store Plan Forcing: You Can’t Always Get What You Want

Kendra Little shows an example where trying to force a Query Store plan results in an oddity: This is not considered a “failure” When I check the Query Store DMVs, force_failure_count is 0. The last_force_failure_reason_desc is NONE. Query Store didn’t fail to apply the narrow plan. Instead, it’s just deciding not to give it to […]

Read More

Using The GROUPING SETS Operator

Alfonso Hernandez goes into detail with what you can do with GROUPING SETS: In T-SQL, you summarize data by using the GROUP BY clause within an aggregate query. This clause creates groupings which are defined by a set of expressions. One row per unique combination of the expressions in the GROUP BY clause is returned, and aggregate functions such […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

November 2017
MTWTFSS
« Oct  
 12345
6789101112
13141516171819
20212223242526
27282930