You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries


Written by: ronen ariely
01/10/2014 10:53 RssIcon

This is small set of script in order to get the connection properties. You can use it in order to compare two execution of the same query on the same database which use different Execution Plan as a result of different Connection Properties.

select * from sys.dm_exec_connections
where session_id = @@SPID
-- The run_value include one INT which is a combination of all the options. More dedail folowing
EXEC sp_configure 'User options'
-- Another option is to use dirrect approach:
    ConnectionProperty('net_transport') AS 'Net transport Protocol',
    ConnectionProperty('local_net_address') AS 'Server IP Address',
    ConnectionProperty('local_tcp_port') AS 'Port',
    ConnectionProperty('protocol_type') AS 'Protocol type',
    ConnectionProperty('client_net_address') AS 'Client IP Address',
    ConnectionProperty('SQL Server Rider') AS 'SQLhelper',
    ConnectionProperty('implicit transactions') AS [implicit transactions],
    ConnectionProperty('cursor close on commit') AS [cursor close on commit],
    ConnectionProperty('ansi warnings') AS [ansi warnings],
    ConnectionProperty('ansi padding') AS [ansi padding],
    ConnectionProperty('ansi nulls') AS [ansi nulls],
    ConnectionProperty('arithmetic abort') AS [arithmetic abort],
    ConnectionProperty('arithmetic ignore') AS [arithmetic ignore],
    ConnectionProperty('quoted identifier') AS [quoted identifier],
    ConnectionProperty('no count') AS [no count],
    ConnectionProperty('ansi null default on') AS [ansi null default on],
    ConnectionProperty('ansi null default off') AS [ansi null default off],
    ConnectionProperty('concat null yields null') AS [concat null yields null],
    ConnectionProperty('numeric round abort') AS [numeric round abort],
    ConnectionProperty('xact_abort on') AS [xact_abort on]
/*************************************** interpretation of EXEC sp_configure 'User options' */
-- Create an accessories table
-- More information:
-- http://msdn.microsoft.com/en-us/library/ms190763.aspx
CREATE TABLE #Ariely_ConnectionOptions_Tbl (Property VARCHAR(100), Value INT)
INSERT #Ariely_ConnectionOptions_Tbl
SELECT 'implicit transactions',     2 UNION ALL
SELECT 'cursor close on commit',    4 UNION ALL
SELECT 'ansi warnings',             8 UNION ALL
SELECT 'ansi padding',              16 UNION ALL
SELECT 'ansi nulls',                32 UNION ALL
SELECT 'arithmetic abort',          64 UNION ALL
SELECT 'arithmetic ignore',         128 UNION ALL
SELECT 'quoted identifier',         256 UNION ALL
SELECT 'no count',                  512 UNION ALL
SELECT 'ansi null default on',      1024 UNION ALL
SELECT 'ansi null default off',     2048 UNION ALL
SELECT 'concat null yields null',   4096 UNION ALL
SELECT 'numeric round abort',       8192 UNION ALL
SELECT 'xact_abort on',             16384
SELECT * FROM #Ariely_ConnectionOptions_Tbl
--Create a table which will get the user options values
CREATE TABLE #Ariely_UserOptions_Tbl ( name VARCHAR(100), minimum INT, maximum INT, config_vale INT, run_value INT)
-- Fill the user options values
INSERT #Ariely_UserOptions_Tbl
EXEC sp_configure 'User options'
-- Check the information
DECLARE @currentvalue INT
SELECT @currentvalue = run_value FROM #Ariely_UserOptions_Tbl
        WHEN (@currentvalue & value) = value THEN 'has been SET'
        ELSE 'NOT SET'
    END CurrentValue
 FROM #Ariely_ConnectionOptions_Tbl
-- Clear
DROP TABLE #Ariely_ConnectionOptions_Tbl
DROP TABLE #Ariely_UserOptions_Tbl

More information: