Getting Set Options for a Session

While browsing StackOverflow, I came across an interesting use case for the Set Options feature in SQL Server. I have created a few YouTube videos on the different SET options available at the database level, see links below. In this case the user wanted to use the @@options function to track the Options that are SET then revert back to the same values at a later point in time. I don’t know why they would be doing this I assume it is for troubleshooting a issue. Event then I am not sure this is the best way to do it. With that said we need to find a quick and easy way to get the Options that are SET when the session is created.

The Trace

There are number of places where you can gather the SET Options, one of the first places a developer sees the SET Options is when they start a Trace in SQL profiler. Unfortunately these values cant be extracted easily and don’t lend them selves easily to troubleshooting. The below screenshot is a example of what you would see in Profiler.

The DMV

There is another way developers can gather the Set Options for a session and this is under the below DMVs, these are again dependent on the context in which the Set Option is applicable. For example some SET options work at the database level while others at the session level.

/*
    This query retrieves all columns from the sys.dm_exec_sessions dynamic management view.

    sys.dm_exec_sessions provides information about all active user connections and internal tasks in SQL Server.

    Common columns in the output include:
      - session_id: Unique identifier for the session.
      - login_time: Time at which the session was established.
      - host_name: Name of the client workstation.
      - program_name: Name of the client application.
      - login_name: Name of the user who established the session.
      - status: Current status of the session (e.g., running, sleeping).
      - cpu_time: Total CPU time (in milliseconds) used by the session.
      - memory_usage: Number of 8-KB pages used by the session.
      - reads: Number of logical reads performed by the session.
      - writes: Number of logical writes performed by the session.
      - last_request_start_time: Time when the last request started.
      - last_request_end_time: Time when the last request finished.
      - is_user_process: Indicates if the session is a user process (1) or system process (0).

    Note: For a full list and detailed explanation of all columns, refer to the official Microsoft documentation:
    https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql
*/
SELECT
    *
FROM
    sys.dm_exec_sessions;

-- Selects all columns from the sys.databases catalog view.
-- Best practice: Specify column names instead of using *, and use aliases for clarity.

SELECT
    name AS database_name,
    database_id,
    state_desc,
    recovery_model_desc,
    compatibility_level,
    create_date
FROM
    sys.databases
ORDER BY
    name;

-- Explanation:
-- This query returns a list of all databases on the SQL Server instance.
-- Columns:
--   database_name: Name of the database.
--   database_id: Unique ID for the database.
--   state_desc: Current state (e.g., ONLINE, OFFLINE).
--   recovery_model_desc: Recovery model (e.g., FULL, SIMPLE).
--   compatibility_level: SQL compatibility level.
--   create_date: When the database was created.

The Query

/*
    This query displays the current session's SET options by decoding the @@OPTIONS bitmask.
    Each column shows if a specific session option is enabled (1) or disabled (0).
*/

WITH SessionOptions AS (
    SELECT
          GET_BIT(@@OPTIONS, 0)  AS [DISABLE_DEF_CNST_CHK]         -- Deferred constraint checking
        , GET_BIT(@@OPTIONS, 1)  AS [IMPLICIT_TRANSACTIONS]        -- Implicit transactions for dblib
        , GET_BIT(@@OPTIONS, 2)  AS [CURSOR_CLOSE_ON_COMMIT]       -- Cursor close on commit
        , GET_BIT(@@OPTIONS, 3)  AS [ANSI_WARNINGS]                -- ANSI warnings
        , GET_BIT(@@OPTIONS, 4)  AS [ANSI_PADDING]                 -- ANSI padding
        , GET_BIT(@@OPTIONS, 5)  AS [ANSI_NULLS]                   -- ANSI nulls
        , GET_BIT(@@OPTIONS, 6)  AS [ARITHABORT]                   -- Abort on arithmetic errors
        , GET_BIT(@@OPTIONS, 7)  AS [ARITHIGNORE]                  -- Ignore arithmetic errors
        , GET_BIT(@@OPTIONS, 8)  AS [QUOTED_IDENTIFIER]            -- Quoted identifier
        , GET_BIT(@@OPTIONS, 9)  AS [NOCOUNT]                      -- No count messages
        , GET_BIT(@@OPTIONS, 10) AS [ANSI_NULL_DFLT_ON]            -- ANSI null default ON
        , GET_BIT(@@OPTIONS, 11) AS [ANSI_NULL_DFLT_OFF]           -- ANSI null default OFF
        , GET_BIT(@@OPTIONS, 12) AS [CONCAT_NULL_YIELDS_NULL]      -- NULL concat yields NULL
        , GET_BIT(@@OPTIONS, 13) AS [NUMERIC_ROUNDABORT]           -- Numeric round abort
        , GET_BIT(@@OPTIONS, 14) AS [XACT_ABORT]                   -- Transaction abort on error
)
SELECT *
FROM SessionOptions;
GO

Some common SET options are available as part of querying the @@OPTIONS function, the problem is the @@Options returns a bit mask which means it requires some decoding to understand which properties are set or unset. The below query shows the way to get and decode the data

For example, my set options is 5496 which when converted to Binary is 101010111100, remember the binary data needs to be reversed in order to map it with the above column list.

A big challenge with using @@Option is it doesn’t show some available options such as date format which we can see in the DMV above. These are set at the Session level so they account for very specific use cases.

In Summary when you want the OPTIONS list for SQL Server try and use the DMV where possible since it gives the maximum number of details and requires hardly any effort or additional coding.

Links

There are more , please watch the playlist for the complete list.