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

By: Tim Ford   |   Updated: 2009-08-17   |   Comments (1)   |   Related: > 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:

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:

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:

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.

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.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips