Decoding Client Options In SQL Server Deadlocks

Has Been

Recently while working with a client to fix some deadlock issues, they asked about the client options stored in the XML.

I could remember looking at them at one point to see if there was a decoder ring for them, but never found anything definitive.

There’s a similar type of bittish thing in dm_exec_plan_attributes. It gets decoded in sp_BlitzCache like so:

SetOptions = SUBSTRING(
             CASE WHEN (CAST(pa.value AS INT) & 1 = 1) THEN ', ANSI_PADDING' ELSE '' END +
             CASE WHEN (CAST(pa.value AS INT) & 8 = 8) THEN ', CONCAT_NULL_YIELDS_NULL' ELSE '' END +
             CASE WHEN (CAST(pa.value AS INT) & 16 = 16) THEN ', ANSI_WARNINGS' ELSE '' END +
             CASE WHEN (CAST(pa.value AS INT) & 32 = 32) THEN ', ANSI_NULLS' ELSE '' END +
             CASE WHEN (CAST(pa.value AS INT) & 64 = 64) THEN ', QUOTED_IDENTIFIER' ELSE '' END +
             CASE WHEN (CAST(pa.value AS INT) & 4096 = 4096) THEN ', ARITH_ABORT' ELSE '' END +
             CASE WHEN (CAST(pa.value AS INT) & 8192 = 8191) THEN ', NUMERIC_ROUNDABORT' ELSE '' END 
             , 2, 200000)

Based on that, I was hopeful that I could use a combination of SSMS and session settings to figure out where bits flip in client options.

I don’t think I got all of them, but this simple demo shows off the ones that were accessible. Some caveats here:

• No one at Microsoft has validated these
• They might change in the future
• There are probably ones I missed

Ring Toss

Here’s what I came up with, using my own experimenting, and also some test data from deadlock XML files I had sitting around.

CREATE TABLE 
    #temptable 
( 
    clientoption1 bigint, 
    clientoption2 bigint 
);

INSERT INTO 
    #temptable 
(
    clientoption1, 
    clientoption2
)
VALUES
(536870944, 128056), 
(671088672, 128056), 
(671088672, 128058), 
(673185824, 128056), 
(673316896, 128056);


SELECT
    q.clientoption1,
    q.clientoption2,
    clientoption1 = 
        SUBSTRING
        (    
            CASE WHEN q.clientoption1 & 0x20 = 0x20 THEN ', QUOTED IDENTIFIER ON' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x40 = 0x40 THEN ', ARITHABORT' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x800 = 0x800 THEN ', USER SET ARITHABORT' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x8000 = 0x8000 THEN ', NUMERIC ROUNDABORT ON' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x10000 = 0x10000 THEN ', USER SET NUMERIC ROUNDABORT ON' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x20000 = 0x20000 THEN ', SET XACT ABORT ON' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x80000 = 0x80000 THEN ', NOCOUNT OFF' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x200000 = 0x200000 THEN ', NOCOUNT ON' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x8000000 = 8000000 THEN ', USER SET QUOTED IDENTIFIER' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x20000000 = 0x20000000 THEN ', ANSI NULL DEFAULT ON' ELSE '' END +
            CASE WHEN q.clientoption1 & 0x40000000 = 0x40000000 THEN ', ANSI NULL DEFAULT OFF' ELSE '' END,
            3,
            8000
        ),
    clientoption2 = 
        SUBSTRING
        (
            CASE WHEN q.clientoption2 & 2 = 2 THEN ', IMPLICIT TRANSACTION' ELSE '' END +
            CASE WHEN q.clientoption2 & 8 = 8 THEN ', ANSI WARNINGS' ELSE '' END +
            CASE WHEN q.clientoption2 & 0x10 = 0x10 THEN ', ANSI PADDING' ELSE '' END +
            CASE WHEN q.clientoption2 & 0x20 = 0x20 THEN ', ANSI NULLS' ELSE '' END +
            CASE WHEN q.clientoption2 & 0x1000 = 0x1000 THEN ', USER CONCAT NULL YIELDS NULL' ELSE '' END +
            CASE WHEN q.clientoption2 & 0x2000 = 0x2000 THEN ', CONCAT NULL YIELDS NULL' ELSE '' END +
            CASE WHEN q.clientoption2 & 0x4000 = 0x4000 THEN ', USER ANSI NULLS' ELSE '' END +
            CASE WHEN q.clientoption2 & 0x8000 = 0x8000 THEN ', USER ANSI WARNINGS' ELSE '' END,
            3,
            8000
        )
FROM #temptable AS q;

I may work on getting these into some deadlock analysis tooling after I have a little more validation that the results are correct.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.