skreebydba

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.