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.
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 "context" we're interested in, has the following definition:
CREATE FUNCTION sys.dm_exec_cursors (@spid int)
RETURNS table
AS
RETURN SELECT *
FROM OpenRowSet(TABLE DM_EXEC_CURSORS, @spid)
As you can see above, this function is really just a wrapper around the system-table DM_EXEC_CURSORS; the definition of system tables like this one are again implemented inside the SQL Server engine and not easily inspected.
The other in-lined table-functions listed in the output of the query above all utilize the same OpenRowSet(TABLE ...) functionality.
The sys.sp_MSsetcontext_bypasswholeddleventbit looks intriguing:
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
However, it only uses SET CONTEXT_INFO and CONTEXT_INFO() to inspect and modify the context info. No luck here.
Perhaps sys.query_context_settings contains some gold? Nope:
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
I also checked the definitions of SQL modules, such as stored procedures, functions, etc, with this query:
SELECT so.name
, asm.definition
FROM sys.all_sql_modules asm
INNER JOIN sys.sysobjects so ON asm.object_id = so.id
WHERE asm.definition LIKE '%context%'
ORDER BY so.name;
None of the items listed in that query contain anything relevant, either.
In short, I could find nothing related to session context. I expect this is by design since the session context could easily be used to store sensitive data for the duration of a session.