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, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.