Find, compare and use the same session settings as another SQL Server user


By:   |   Updated: 2009-08-17   |   Comments (1)   |   Related: More > Testing


Problem
When diagnosing issues in SQL Server I've found that sometimes I need to be able to mimic a user's session state when attempting to repeat an error they may be receiving.  The smallest differences can completely change the outcome, so I need to ensure all the session settings (QUOTED_IDENTIFIER, ANSI_NULLS, and so forth) are identical between the production session and my test session.  Is there an easy way to determine these settings with a single query?

Solution
Absolutely, this is yet another case where the Dynamic Management Views assist the Database Administrator in simplifying a rather mundane task.  A simple query against the sys.dm_exec_sessions DMV will provide you with the session-wide settings for all sessions currently active on the SQL Server instance.  The first iteration of the query looks like this:

SELECT SDES.[session_id]SDES.[login_name],
SDES.[text_size]SDES.[language]SDES.[date_format]SDES.[date_first]
 
CASE SDES.[date_first]
   
WHEN THEN 'Monday'
   
WHEN THEN 'Tuesday'
   
WHEN THEN 'Wednesday'
   
WHEN THEN 'Thursday'
   
WHEN THEN 'Friday'
   
WHEN THEN 'Saturday'
   
WHEN THEN 'Sunday (default)'
 
END AS [date_first_desc]SDES.[quoted_identifier]SDES.[arithabort]
    
SDES.[ansi_null_dflt_on]SDES.[ansi_defaults]SDES.[ansi_warnings]SDES.[ansi_padding]
    
SDES.[ansi_nulls]SDES.[concat_null_yields_null]SDES.[transaction_isolation_level]
    
CASE SDES.[transaction_isolation_level]
   
WHEN THEN 'Unspecified'
   
WHEN THEN 'READUNCOMMITTED'
   
WHEN THEN 'READCOMMITTED'
   
WHEN THEN 'REPEATABLE'
   
WHEN THEN 'SERIALIZABLE'
   
WHEN THEN 'SNAPSHOT'
 
END AS [transaction_isolation_level_desc],
    
SDES.[lock_timeout]SDES.[deadlock_priority]  
FROM sys.[dm_exec_sessions] SDES 
WHERE SDES.[session_id] 50
ORDER BY SDES.[session_id]

Note: the result set is broken apart to make it easier to read.

This query returns the session-wide settings for all sessions on the SQL Server instance. You'll note that I excluded any of the system-generated sessions from the result set.  The columns returned relate to the session-wide configurable settings that control how a session interacts with the query engine as well as other sessions.  A brief explanation of each is listed below.  For more detailed information please consult the links exposed in the Next Steps section of this tip.

  • session_id, login_name: the session_id uniquely identify the session on the MS SQL Server instance, the login_name merely fleshes-out the login that instantiated the session and is informational only.
  • text_size:  determines the length of the BLOB data type columns (text, ntext, image, varchar(max), nvarchar(max) and varbinary(max)) returned via a SELECT statement.
  • language:  sets, just as you would expect, the language for the session.  As stated in Books Online this also will affect datetime presentation.
  • date_format:  determines order of Year, Month, and Day presented in the date and time-based data types.
  • date_first, date_first_desc:  Identifies which day of the week is considered to occur first in any calculations that require presentation or calculation of a weekday.  The value of 7 signifies Sunday, which is the default value.
  • quoted_identifier:  Depending upon the setting, disregards or enforces ISO requirements for quoted identifiers and string literals.
  • arithabort:  A value of 1 (ON) signifies that a query will be terminated when an overflow or divide-by-zero error occurs during execution.
  • ansi_null_dflt_on:  This setting determines if NULLS are allowed by default for any new column created when nullability is not explicitly set in a CREATE TABLE or ALTER TABLE DDL statement.
  • ansi_defaults:  Controls seven separate session settings when configured either ON or OFF.  Those settings are:
    • SET ANSI_NULLS
    • SET ANSI_NULL_DFLT_ON
    • SET ANSI_PADDING
    • SET ANSI_WARNINGS
    • SET CURSOR_CLOSE_ON_COMMIT
    • SET IMPLICIT_TRANSACTIONS
    • SET QUOTED_IDENTIFIER
  • ansi_warnings:  Governs warning and query behavior when NULLS are encountered in aggregate functions or in mathematical errors such as divide-by-zero.
  • ansi_padding:  Controls how SQL stores values smaller than the max size of a data type and how trailing spaces are handled.
  • ansi_nulls:  Specifies how equality and inequality is handled when dealing with NULL values.
  • concat_null_yields_null:  Controls how NULLs affect concatenation.
  • transaction_isolation_level, transaction_isolation_level_desc:  Specifies the transaction isolation setting for the session. 
  • lock_timeout:  the LOCK_TIMEOUT setting for the session, in milliseconds.
  • deadlock_priority:  On a scale of -10 to +10 the priority of a session when encountering or involved in a deadlock.  -10 meaning there is almost 100% certainty that this session will be declared the victim (LOW priority).

You can further-refine the query to satisfy your needs by first identifying the user session you wish to emulate, via a generic call to sys.dm_exec_sessions:

SELECT 
FROM sys.dm_exec_sessions 
WHERE [login_name] 'user name identified'

Afterwards you would change the first query's WHERE clause as follows, substituting the session_id you've identified for the <identified_session_id> marker and comparing that to your current session via the @@spid system variable:

WHERE SDES.[session_id] <identified_session_id> OR SDES.[session_id] @@SPID

The result set would provide values for your current session and also the one you are trying to mimic.  This way you can easily compare the values between the two sessions.

Here is an example of this query where I want to compare my session to session 52.

SELECT SDES.[session_id]SDES.[login_name],
SDES.[text_size]SDES.[language]SDES.[date_format]SDES.[date_first]
 
CASE SDES.[date_first]
   
WHEN THEN 'Monday'
   
WHEN THEN 'Tuesday'
   
WHEN THEN 'Wednesday'
   
WHEN THEN 'Thursday'
   
WHEN THEN 'Friday'
   
WHEN THEN 'Saturday'
   
WHEN THEN 'Sunday (default)'
 
END AS [date_first_desc]SDES.[quoted_identifier]SDES.[arithabort]
    
SDES.[ansi_null_dflt_on]SDES.[ansi_defaults]SDES.[ansi_warnings]SDES.[ansi_padding]
    
SDES.[ansi_nulls]SDES.[concat_null_yields_null]SDES.[transaction_isolation_level]
    
CASE SDES.[transaction_isolation_level]
   
WHEN THEN 'Unspecified'
   
WHEN THEN 'READUNCOMMITTED'
   
WHEN THEN 'READCOMMITTED'
   
WHEN THEN 'REPEATABLE'
   
WHEN THEN 'SERIALIZABLE'
   
WHEN THEN 'SNAPSHOT'
 
END AS [transaction_isolation_level_desc],
    
SDES.[lock_timeout]SDES.[deadlock_priority]  
FROM sys.[dm_exec_sessions] SDES 
WHERE SDES.[session_id] 52 OR SDES.[session_id] @@SPID --this will show just the two sessions
ORDER BY SDES.[session_id]

Next Steps

  • Read more tips by the author here.
  • Looking for other options when querying session setting information.  Greg Robidoux provides this interesting article.
  • Interested in the Dynamic Management Views and Functions?  Here are some more tips.


Last Updated: 2009-08-17


get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips





Comments For This Article




Monday, August 31, 2009 - 2:51:45 PM - AK1 Back To Top (3981)

 Tim,

Can you include READ COMMITTED SNAPSHOT so that all isolation levels are displayed?

 



download





Recommended Reading

Clearing Cache for SQL Server Performance Testing

SQL Server Database Requirements

Attach Sample Database - Adventureworks in SQL Server 2012

SQL Query Stress Tool

Populate Large Tables with Random Data for SQL Server Performance Testing








get free sql tips
agree to terms


Learn more about SQL Server tools