fn_setopts()

version: 1.0.0.1

last updated: 13 April 2005

platform: MSDE 2000 and SQL Server 2000 running Windows NT 4.0 and higher

Description:

SET options are a key factor in determining the actual query execution plan. However SET options do not only play an important role at query execution time, also at object creation time, they determine the behavior of database objects when queried.

Therefore it is very important to use a consistent set of "SET options" in your environment and across all tools and applications used; both when creating objects and when executing queries which reference/access these database objects! Not using a single consistent set of "SET options" can lead to performance related problems which can be hard to determine.

If you are not familiar with the magic 7 SET options that affect your results, please read SQL Server Books Online "SET Options That Affect Results"

SET options and database objects

The SET options that were used when the objects were created are stored as part of the object meta data and can be retrieved using the ObjectProperty function.

There are two options of that can be retrieved: ExecIsQuotedIdentOn and ExecIsAnsiNullsOn, which can be retrieved like this:

select  objectproperty(object_id(N'[owner].[objectname]'), N'ExecIsQuotedIdentOn'),
        objectproperty(object_id(N'[owner].[objectname]'), N'ExecIsAnsiNullsOn')

SET options and connections

For a connection there are different ways of figuring out what the currently set "SET options" are.

SQL Trace

You can use SQL Trace; both the Login and ExistingConnection event will provide the values of the "SET options" that are turned on as part of the TextData column.

-- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7

However this only provides the state of the SET options for th econnection when the connection was established or for the ExistingConnection event it reflects the state of the SET options when the trace was turned on. Changes to the SET options are send to the server via T-SQL commands or through TDS.

dbcc useroptions

dbcc useroptions returns the SET options active (set) for the current connection. The result looks like this:

Set Option              Value
----------------------- ----------------------------------------------
textsize                64512
language                us_english
dateformat              mdy
datefirst               7
quoted_identifier       SET
arithabort              SET
ansi_null_dflt_on       SET
ansi_defaults           SET
ansi_warnings           SET
ansi_padding            SET
ansi_nulls              SET
concat_null_yields_null SET

@@options

The global session variable @@options returns a bitmap of the SET options that are active for the current connection. The bitmap can be decoded with the following T-SQL query:

select @@options
go

IF @@OPTIONS & 1     > 0 print 'This SET option is obsolete'
IF @@OPTIONS & 2     > 0 print 'IMPLICIT_TRANSACTIONS'
IF @@OPTIONS & 4     > 0 print 'CURSOR_CLOSE_ON_COMMIT'
IF @@OPTIONS & 8     > 0 print 'ANSI_WARNINGS'
IF @@OPTIONS & 16    > 0 print 'ANSI_PADDING'
IF @@OPTIONS & 32    > 0 print 'ANSI_NULLS'
IF @@OPTIONS & 64    > 0 print 'ARITHABORT'
IF @@OPTIONS & 128   > 0 print 'ARITHIGNORE'
IF @@OPTIONS & 256   > 0 print 'QUOTED_IDENTIFIER'
IF @@OPTIONS & 512   > 0 print 'NOCOUNT'
IF @@OPTIONS & 1024  > 0 print 'ANSI_NULL_DFLT_ON'
IF @@OPTIONS & 2048  > 0 print 'ANSI_NULL_DFLT_OFF'
IF @@OPTIONS & 4096  > 0 print 'CONCAT_NULL_YIELDS_NULL'
IF @@OPTIONS & 8192  > 0 print 'NUMERIC_ROUNDABORT'
IF @@OPTIONS & 16384 > 0 print 'XACT_ABORT'
go

-- bitmap value per SET option:
SET IMPLICIT_TRANSACTIONS OFF      -- 2
SET CURSOR_CLOSE_ON_COMMIT OFF     -- 4
SET ANSI_WARNINGS OFF              -- 8
SET ANSI_PADDING OFF               -- 16
SET ANSI_NULLS OFF                 -- 32
SET ARITHABORT OFF                 -- 64
SET ARITHIGNORE OFF                -- 128
SET QUOTED_IDENTIFIER OFF          -- 256
SET NOCOUNT OFF                    -- 512
SET ANSI_NULL_DFLT_ON OFF          -- 1024
SET ANSI_NULL_DFLT_OFF OFF         -- 2048
SET CONCAT_NULL_YIELDS_NULL OFF    -- 4096
SET NUMERIC_ROUNDABORT OFF         -- 8192
SET XACT_ABORT OFF                 -- 16384
 
setopts column in master.dbo.syscacheobjects

When a query plan is created a rows are added to the master.dbo.syscacheobjects table, which represent the read only compiled plan and the executable plan of which there is one per concurrent execution. Note that trivial query plans plans are not cached and therefore will not show up as a row in the syscacheobjects table. Part of the plan which SQL Server stores are the "SET options" which we used when the plan was created, in order to achieve plan reuse, one of the determining factors is, if sessions use the same "SET options". The SET options are decoded in a bitmap column named setopts.

To determine all different SET options used simply use the following query:

select distinct setopts from master.dbo.syscacheobjects
 

You might expect that the setopt bitmap can be decoded using the same values as the @@options bitmap, however this is not the case, which is why the function fn_setopts was created.

create function dbo.fn_setopts(@setopts int)
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
if @setopts & 1    > 0 select @s = @s + N'ANSI_PADDING, '
if @setopts & 2    > 0 select @s = @s + N'max degree of parallelism, '
if @setopts & 4    > 0 select @s = @s + N'FORCEPLAN, '
if @setopts & 8    > 0 select @s = @s + N'CONCAT_NULL_YIELDS_NULL, '
if @setopts & 16   > 0 select @s = @s + N'ANSI_WARNINGS, '
if @setopts & 32   > 0 select @s = @s + N'ANSI_NULLS, '
if @setopts & 64   > 0 select @s = @s + N'QUOTED_IDENTIFIER, '
if @setopts & 128  > 0 select @s = @s + N'ANSI_NULL_DFLT_ON, '
if @setopts & 256  > 0 select @s = @s + N'ANSI_NULL_DFLT_OFF, '
if @setopts & 512  > 0 select @s = @s + N'NO_BROWSETABLE, '
if @setopts & 1024 > 0 select @s = @s + N'TriggerOneRow, '
if @setopts & 2048 > 0 select @s = @s + N'ResyncQuery, '
if @setopts & 4096 > 0 select @s = @s + N'ARITHABORT, '
if @setopts & 8192 > 0 select @s = @s + N'NUMERIC_ROUNDABORT, '

return @s
end
go
 

Usage of the function is very simple:

select   distinct setopts,
         [SET Options] = dbo.fn_setopts(setopts)
from     master.dbo.syscacheobjects
order by setopts
 

Result set:

This way you can quickly spot different SET options used by various query plans.

Download:

• fn_setopts.sql

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.