Monday, February 4, 2008

Session Settings for SSB Activation Procedure

According to BOL, the session settings for internal activation are the default databases options:
"Service Broker executes internally activated service programs on a background session distinct from the connection that created the message. The options set for this session are the default options for the database."
Pasted from <>

The default options for out database are "OFF" for all the relevant settings. But the activation procedure when activated reports that the followings settings are turned "ON":

Where did these settings come from? ANSI_NULLS and QUOTED_IDENTIFIER are understandable because they are creation time settings. The others are not set as the database defaults.

First, I thought those settings are from the set_options attribute of the cached plan. But the following query
SELECT P.usecounts, A.set_options FROM sys.dm_exec_cached_plans P OUTER APPLY sys.dm_exec_plan_attributes(P.plan_handle) A
CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) T
WHERE A.attribute = 'set_options'
AND P.cacheobjtype = 'Compiled Plan' AND P.objtype = 'Proc'
AND T.text LIKE '%procName%'; -- replace procName with the name of the activation procedure
reports that this set of sestings are compiled with the cached plan:

So that is not it. What is most strange about the set_options of the cached plan is IMPLICIT_TRANSACTIONS which should not be set at all in normal situations because of its unexpected effects on @@TRANCOUNT.

The settings reported from within the activation procedure are the desired behavior settings with the exception of missing ARITHABORT but it is implicitly implied by ANSI_WARNINGS. Also, these 6 settings are exactly the same as those for an ADO.Net connection. These 6 except for CONCAT_NULL_YIELDS_NULL belong to the group of settings controled by ANSI_DEFAULTS. ANSI_DEFAULTS include two other settings: CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS. The following is quoted from BOL:
"The SQL Native Client ODBC driver and SQL Native Client OLE DB Provider for SQL Server automatically set ANSI_DEFAULTS to ON when connecting. The driver and Provider then set CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS to OFF. The OFF settings for SET CURSOR_CLOSE_ON_COMMIT and SET IMPLICIT_TRANSACTIONS can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server. The default for SET ANSI_DEFAULTS is OFF for connections from DB-Library applications."

So it seems that instead of using database default options, SSB activation procedure is executed in a session environment similar to the connection of an SQL Native Client OLE DB Provider (such as SSMS client).

No comments: