Archive
Return SQL Server Context Settings from sys.query_context_settings
Today, I attended Erin Stellato's (b|t) precon session Solving Common Performance Problems Using Query Store. In her presentation, Erin showed that the same query text can return multiple results in Query Store if the context settings for the queries are different. The context settings can be found in the sys.query_context_settings DMV in the set_options column. set_options is stored as a hex value. 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.
/* Change database context if you want to create the procedure in a database other than master */ USE master; SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP PROCEDURE IF EXISTS ReturnSetOptions; GO /* ============================================= -- Author: Frank Gill -- Create date: 2017-10-30 -- Description: Stored Procedure to return SET options for a INT context_settings value -- Sample Execution: EXEC ReturnSetOptions @ContextSettings = 251; -- =============================================*/ CREATE PROCEDURE ReturnSetOptions @ContextSettings INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DROP TABLE IF EXISTS #binaryvalues; /* Create temp table to hold each binary position and its associated context setting */ CREATE TABLE #binaryvalues (RowId INT IDENTITY(1,1) ,BinaryValue INT ,BinaryFlag BIT ,SetOption SYSNAME); /* Insert context setting information into temp table Values found here https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-plan-attributes-transact-sql*/ INSERT INTO #binaryvalues (BinaryValue ,BinaryFlag ,SetOption) VALUES (1,0,N'ANSI_PADDING'), (2,0,N'Parallel Plan'), (4,0,N'FORCEPLAN'), (8,0,N'CONCAT_NULL_YIELDS_NULL'), (16,0,N'ANSI_WARNINGS'), (32,0,N'ANSI_NULLS'), (64,0,N'QUOTED_IDENTIFIER'), (128,0,N'ANSI_NULL_DFLT_ON'), (256,0,N'ANSI_NULL_DFLT_OFF'), (512,0,N'NoBrowseTable'), (1024,0,N'TriggerOneRow'), (2048,0,N'ResyncQuery'), (4096,0,N'ARITH_ABORT'), (8192,0,N'NUMERIC_ROUNDABORT'), (16384,0,N'DATEFIRST'), (32768,0,N'DATEFORMAT'), (65536,0,N'LanguageID'), (131072,0,N'UPON'), (262144,0,N'ROWCOUNT'); --SELECT * FROM #binaryvalues; /* Declare local variables */ DECLARE @RowId INT, @BinaryValue INT, @ModValue INT, @DivValue INT, @OddValue INT, @MaxRowId INT, @BinaryChar CHAR(1), @BinaryString VARCHAR(MAX) = ''; /* Determine if input value is odd Used for the one position of the binary value */ SELECT @OddValue = @ContextSettings % 2; /* Return the largest value and row id from the temp table less than the input value */ SELECT @RowId = RowId, @BinaryValue = BinaryValue FROM #binaryvalues WHERE BinaryValue <= @ContextSettings; /* Loop backwards through the rows in the temp table chacking to see if the binary position should be set */ WHILE @RowId > 1 BEGIN /* Calculate the modulus and dividend for the largest binary value */ SELECT @ModValue = @ContextSettings % @BinaryValue; SELECT @DivValue = @ContextSettings / @BinaryValue; /* If the dividend is 1, the current binary position is set to 1*/ IF @DivValue = 1 BEGIN UPDATE #binaryvalues SET BinaryFlag = 1 WHERE RowId = @RowId; END /* Update the input value to the modulus for use in the next calculation */ SELECT @ContextSettings = @ModValue; /* If @ContextSettings is greater than 1 get the previous values from the temp table Otherwise, short circuit the loop */ IF @ContextSettings > 1 BEGIN SELECT @RowId = RowId, @BinaryValue = BinaryValue FROM #binaryvalues WHERE BinaryValue <= @ContextSettings; END ELSE BEGIN SELECT @RowId = 1; END END /* If the input value is odd, set the 1 position to 1 */ IF @OddValue = 1 BEGIN UPDATE #binaryvalues SET BinaryFlag = 1 WHERE BinaryValue = 1; END /* Get the max row with a binary position set */ SELECT @MaxRowId = MAX(RowId) FROM #binaryvalues WHERE BinaryFlag = 1; /* Loop through and concatenate the binary flag values to generate the binary number equivalent to the input value */ WHILE @MaxRowId >= 1 BEGIN SELECT @BinaryChar = CAST(BinaryFlag AS CHAR(1)) FROM #binaryvalues WHERE RowId = @MaxRowId SELECT @BinaryString = CONCAT(@BinaryString, @BinaryChar) SELECT @MaxRowId -= 1; END /* Select the binary number */ SELECT @BinaryString AS BinaryValue; /* Select the set options indicated by the binary number */ SELECT SetOption FROM #binaryvalues WHERE BinaryFlag = 1; END GO
Thanks to Erin for an excellent and informative training session.