HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Find which SESSION_CONTEXT key-value pairs was set

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
session_contextvaluewasfindwhichpairssetkey

Problem

I found that the following query shows how many session context variables was set:

SELECT cache_address
,name
,pages_kb
,pages_in_use_kb
,entries_count
,entries_in_use_count
FROM sys.dm_os_memory_cache_counters
WHERE TYPE = 'CACHESTORE_SESSION_CONTEXT';


But now I would like to know which key-value pairs of SESSION_CONTEXT() was set -- if any.

After doing extensive research on microsoft sites I didn't found anything related.

Thanks in advance.

Solution

I've checked the definition of many context-related system views, functions, and stored procedures, and cannot locate any further details covering session context variables.

For instance, the sys.dm_os_memory_cache_counters dynamic management view referenced in your question has the following definition:

CREATE VIEW sys.dm_os_memory_cache_counters AS
    SELECT *
    FROM OpenRowSet(TABLE SYSMEMORYCACHECOUNTERS)


The definition of SYSMEMORYCACHECOUNTERS system table is not visible directly, even from the dedicated administrator connection, or DAC, which is therefore a dead-end.

I used this query to look for context-related system objects:

;WITH types AS
(
    SELECT v.xType
        , v.TypeDescription
    FROM (VALUES 
          ('AF', 'Aggregate function (CLR)')
        , ('C', 'CHECK constraint')
        , ('D', 'Default or DEFAULT constraint')
        , ('F', 'FOREIGN KEY constraint')
        , ('L', 'Log')
        , ('FN', 'Scalar function')
        , ('FS', 'Assembly (CLR) scalar-function')
        , ('FT', 'Assembly (CLR) table-valued function')
        , ('IF', 'In-lined table-function')
        , ('IT', 'Internal table')
        , ('P', 'Stored procedure')
        , ('PC', 'Assembly (CLR) stored-procedure')
        , ('PK', 'PRIMARY KEY constraint (type is K)')
        , ('RF', 'Replication filter stored procedure')
        , ('S', 'System table')
        , ('SN', 'Synonym')
        , ('SQ', 'Service queue')
        , ('TA', 'Assembly (CLR) DML trigger')
        , ('TF', 'Table function')
        , ('TR', 'SQL DML Trigger')
        , ('TT', 'Table type')
        , ('U', 'User table')
        , ('UQ', 'UNIQUE constraint (type is K)')
        , ('V', 'View')
        , ('X', 'Extended stored procedure')
    )v(xType,TypeDescription)
)
, sc AS
(
    SELECT so.id
        , ColumnList = STUFF(((SELECT ', ' + sc.name FROM sys.syscolumns sc WHERE sc.id = so.id FOR XML PATH(''))), 1, 2, '')
    FROM sys.sysobjects so
)
SELECT t.TypeDescription
    , so.name
    --, sc.ColumnList
FROM sys.sysobjects so
    LEFT JOIN sc ON so.id = sc.id
    LEFT JOIN types t ON so.xtype = t.xType
WHERE so.name LIKE '%context%'
    OR sc.ColumnList LIKE '%context%'
ORDER BY t.TypeDescription
    , so.name;


in SQL Server 2016, the result of the query is:

╔═══════════════════════════╦════════════════════════════════════════╗
║ Extended stored procedure ║ sp_reset_session_context ║
╠═══════════════════════════╬════════════════════════════════════════╣
║ Extended stored procedure ║ sp_set_session_context ║
║ Extended stored procedure ║ sp_try_set_session_context ║
║ In-lined table-function ║ dm_exec_cursors ║
║ In-lined table-function ║ fn_dblog ║
║ In-lined table-function ║ fn_dblog_xtp ║
║ In-lined table-function ║ fn_dump_dblog ║
║ In-lined table-function ║ fn_dump_dblog_xtp ║
║ Internal table ║ plan_persist_context_settings ║
║ Internal table ║ plan_persist_query ║
║ Stored procedure ║ sp_MSadd_repl_error ║
║ Stored procedure ║ sp_MSsetcontext_bypasswholeddleventbit ║
║ Stored procedure ║ sp_MSsetcontext_replagent ║
║ Stored procedure ║ sp_sqlagent_verify_database_context ║
║ View ║ dm_db_task_space_usage ║
║ View ║ dm_exec_query_stats ║
║ View ║ dm_exec_requests ║
║ View ║ dm_exec_sessions ║
║ View ║ dm_filestream_file_io_handles ║
║ View ║ dm_filestream_file_io_requests ║
║ View ║ dm_filestream_non_transacted_handles ║
║ View ║ dm_os_memory_cache_entries ║
║ View ║ dm_os_schedulers ║
║ View ║ dm_os_tasks ║
║ View ║ dm_os_threads ║
║ View ║ dm_os_waiting_tasks ║
║ View ║ dm_os_workers ║
║ View ║ dm_tran_locks ║
║ View ║ query_context_settings ║
║ View ║ query_store_query ║
║ View ║ sysprocesses ║
╚═══════════════════════════╩════════════════════════════════════════╝

-
Extended stored procedures are implemented in various SQL Server DLLs, and are not easily viewable without reverse-engineering; I've considered them to be black boxes for the purpose of this question.

-
sys.dm_exec_cursors, while pretty clearly not directly related to the "con

Code Snippets

CREATE VIEW sys.dm_os_memory_cache_counters AS
    SELECT *
    FROM OpenRowSet(TABLE SYSMEMORYCACHECOUNTERS)
;WITH types AS
(
    SELECT v.xType
        , v.TypeDescription
    FROM (VALUES 
          ('AF', 'Aggregate function (CLR)')
        , ('C', 'CHECK constraint')
        , ('D', 'Default or DEFAULT constraint')
        , ('F', 'FOREIGN KEY constraint')
        , ('L', 'Log')
        , ('FN', 'Scalar function')
        , ('FS', 'Assembly (CLR) scalar-function')
        , ('FT', 'Assembly (CLR) table-valued function')
        , ('IF', 'In-lined table-function')
        , ('IT', 'Internal table')
        , ('P', 'Stored procedure')
        , ('PC', 'Assembly (CLR) stored-procedure')
        , ('PK', 'PRIMARY KEY constraint (type is K)')
        , ('RF', 'Replication filter stored procedure')
        , ('S', 'System table')
        , ('SN', 'Synonym')
        , ('SQ', 'Service queue')
        , ('TA', 'Assembly (CLR) DML trigger')
        , ('TF', 'Table function')
        , ('TR', 'SQL DML Trigger')
        , ('TT', 'Table type')
        , ('U', 'User table')
        , ('UQ', 'UNIQUE constraint (type is K)')
        , ('V', 'View')
        , ('X', 'Extended stored procedure')
    )v(xType,TypeDescription)
)
, sc AS
(
    SELECT so.id
        , ColumnList = STUFF(((SELECT ', ' + sc.name FROM sys.syscolumns sc WHERE sc.id = so.id FOR XML PATH(''))), 1, 2, '')
    FROM sys.sysobjects so
)
SELECT t.TypeDescription
    , so.name
    --, sc.ColumnList
FROM sys.sysobjects so
    LEFT JOIN sc ON so.id = sc.id
    LEFT JOIN types t ON so.xtype = t.xType
WHERE so.name LIKE '%context%'
    OR sc.ColumnList LIKE '%context%'
ORDER BY t.TypeDescription
    , so.name;
CREATE FUNCTION sys.dm_exec_cursors (@spid int)
RETURNS table
AS
    RETURN SELECT *
    FROM OpenRowSet(TABLE DM_EXEC_CURSORS, @spid)
create procedure sys.sp_MSsetcontext_bypasswholeddleventbit @onoff bit -- 1 to turn on
as
begin
    declare @cur_context varbinary(128)
    declare @cur_context_first_byte binary(1)
    declare @bitmask tinyint
    declare @retcode int

    /*
    ** Security Check
    */
    EXEC @retcode = sys.sp_MSreplcheck_subscribe_withddladmin
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    -- bit to set: snapshot=1, logreader=2, distrib=4, merge=8, 
    -- replication_agent=16, merge_identityrange_alterconstraint=32
    -- merge_bypasswholeddleventbit=64
    if @onoff=1
        set @bitmask=64
    else
        set @bitmask=255-64

    -- get the current context_info. remember we only want to modify a bit without changing the rest of the info
    select @cur_context = isnull(context_info(),0x00)

    -- get the first byte out. the replication agent flags are set in the first byte.
    select @cur_context_first_byte = substring(@cur_context, 1, 1)
    -- set the appropriate bit in this one byte (leaving other bits unchanged).
    if @onoff=1
        select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) | @bitmask)
    else
        select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) & @bitmask)

    -- replace the first byte of the 128 byte binary variable, so that now it has the appropriate bit set.
    select @cur_context = convert(varbinary(128),stuff (@cur_context, 1, 1, @cur_context_first_byte))
    -- set the context_info again with the new binary(128) value.
    set context_info @cur_context

    if @@error <> 0
        return 1

    return 0
end
CREATE VIEW sys.query_context_settings AS
    SELECT
        context_settings_id,
        CONVERT(varbinary(8), set_options) AS 'set_options',
        language_id,
        date_format,
        date_first,
        CONVERT(varbinary(2), status) AS 'status',
        required_cursor_options,
        acceptable_cursor_options,
        merge_action_type,
        default_schema_id,
        is_replication_specific,
        CONVERT(varbinary(1), status2) AS 'is_contained'
    FROM (
        SELECT * FROM sys.plan_persist_context_settings
        UNION ALL
        SELECT TOP 0 * FROM OpenRowSet(TABLE QUERY_STORE_CONTEXT_SETTINGS)
    ) AS ContextSettings

Context

StackExchange Database Administrators Q#188868, answer score: 10

Revisions (0)

No revisions yet.