We recently had to transfer a utility database from our loal development server to a remote server hosted at our data center on the East Coast. We backed up the local database and restored it on the remote server. All went smoothly and DBCC CHECKDB('…') showed no errors. But when we tried to use the CLR functions inside the restored DB, some worked while other did not. A quick analysis showed that those that worked were the simple ones whose assemblies only referenced the .NET assemblies "approved " for SQL, and those that did not work had their assemblies reference .NET assemblies not in the "approved" list, such as System.EnterpriseServices. To give some background, we need those "unapproved" assemblies for our .NET remoting client applications inside SQL; basically, we have made SQL a .NET remoting client which consumes data services with endpoints at our application server. Examples of such data services: SSN decoder, VIN decoder, Address parser, Geo decoder, White Page search engine. All those data services have been implemented as .NET remoting objects hosted in Windows services and are accessed via TCP channels.
Back to our topic. In this particular case, the local server was 32-bit while the remote server 64-bit. It turns out that SQLCLR automatically loads the (most recent) "approved" assemblies in the server's GAC. So, the 64-bit SQL server always has the newest 64-bit .NET assemblies in the "approved" list ready, and since our user assemblies were built to target "Any" platforms, it is no wonder that the CLR functions whose assemblies reference only the "apprioved" assemblies worked immediately after the restore.
The case with "unapproved" assemblies is another story. Their loading is outside SQLCLR's domain, and we have to manually load them. SQLCLR checks the signature of a loaded assembly against that in GAC when the loaded assembly is accessed and reports error if the signatures do not match, for example, when there is an update. Obviously, the 32-bit assembly loaded in the backup could not match its 64-bit counterpart. To fix the problem, ALTER ASSEMBLY [System.EnterpriseServices] FROM … did not work because it was not a compatible upgrade. We ended up dropping all custom assemblies referencing [System.EnterpriseServices] and [System.EnterpriseServices] itself, and recreating all of them in the reverse order.
Thursday, March 20, 2008
Friday, March 7, 2008
Output Parameter for sp_executesql
sp_executesql is the preferred way for running dynamic queries because it encourages parametization which in turn increases the chances of plan reuse.
Here I will discuss a gotcha when using sp_executesql with output parameters and in a loop. Instead of initializing the output parameters in each iteration of the loop, the value from the previous iteration is kept. Let's look at this simple example:
---- Script 1: Output parameter is not initialization
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
In Script 1, we have a WHILE loop for 2 iterations. The simple dynamic query as defined in @vcSQL has two parameters @i and @j as indicated in the execution of sp_executesql , with @i the OUTPUT parameter and @j the normal input parameter.
In the first iteration, @iCount = 1 which is passed to parameter @j. @i starts as NULL and therefore is assigned @j = 1. So, SELECT @iCount, @iOut; produces (1, 1).
In the second iteration, @iCount = 2 which again is assigned to @j, if @i were initialized, then just as in the first iteration, @i would be assigned @j=1, and SELECT @iCount, @iOut; would produce (2, 2). But actually, the result is (2, 1). That shows that at the start of iteration 2, @i was not initialized to NULL, but kept its previous value of 1 from iteration 1.
To make sure the output parameter is initialized in each iteration, we need to explicitly assign it a NULL value at the beginning inside the dynamic query as shown in Script 2. Running it produces the desired (1, 1) and (2, 2) from SELECT @iCount, @iOut; after the two iterations.
---- Script 2:
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = NULL;
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
The above is a very simple demonstration. In the real world, @i might participate complex calculations. For example, it might be an output parameter of a stored procedure such as @expected_rowcount of sp_table_validation which behaves differently depending on whether or not @expected_rowcount is NULL. Knowing the difference with and without the explicit initialization will help us achieve the desired results.
Here I will discuss a gotcha when using sp_executesql with output parameters and in a loop. Instead of initializing the output parameters in each iteration of the loop, the value from the previous iteration is kept. Let's look at this simple example:
---- Script 1: Output parameter is not initialization
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
In Script 1, we have a WHILE loop for 2 iterations. The simple dynamic query as defined in @vcSQL has two parameters @i and @j as indicated in the execution of sp_executesql , with @i the OUTPUT parameter and @j the normal input parameter.
In the first iteration, @iCount = 1 which is passed to parameter @j. @i starts as NULL and therefore is assigned @j = 1. So, SELECT @iCount, @iOut; produces (1, 1).
In the second iteration, @iCount = 2 which again is assigned to @j, if @i were initialized, then just as in the first iteration, @i would be assigned @j=1, and SELECT @iCount, @iOut; would produce (2, 2). But actually, the result is (2, 1). That shows that at the start of iteration 2, @i was not initialized to NULL, but kept its previous value of 1 from iteration 1.
To make sure the output parameter is initialized in each iteration, we need to explicitly assign it a NULL value at the beginning inside the dynamic query as shown in Script 2. Running it produces the desired (1, 1) and (2, 2) from SELECT @iCount, @iOut; after the two iterations.
---- Script 2:
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = NULL;
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
The above is a very simple demonstration. In the real world, @i might participate complex calculations. For example, it might be an output parameter of a stored procedure such as @expected_rowcount of sp_table_validation which behaves differently depending on whether or not @expected_rowcount is NULL. Knowing the difference with and without the explicit initialization will help us achieve the desired results.
Output Parameter for sp_executesql
sp_executesql is the preferred way for running dynamic queries because it encourages parametization which in turn increases the chances of plan reuse.
Here I will discuss a gotcha when using sp_executesql with output parameters and in a loop. Instead of initializing the output parameters in each iteration of the loop, the value from the previous iteration is kept. Let's look at this simple example:
---- Script 1: Output parameter is not initialization
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
In Script 1, we have a WHILE loop for 2 iterations. The simple dynamic query as defined in @vcSQL has two parameters @i and @j as indicated in the execution of sp_executesql , with @i the OUTPUT parameter and @j the normal input parameter.
In the first iteration, @iCount = 1 which is passed to parameter @j. @i starts as NULL and therefore is assigned @j = 1. So, SELECT @iCount, @iOut; produces (1, 1).
In the second iteration, @iCount = 2 which again is assigned to @j, if @i were initialized, then just as in the first iteration, @i would be assigned @j=1, and SELECT @iCount, @iOut; would produce (2, 2). But actually, the result is (2, 1). That shows that at the start of iteration 2, @i was not initialized to NULL, but kept its previous value of 1 from iteration 1.
To make sure the output parameter is initialized in each iteration, we need to explicitly assign it a NULL value at the beginning inside the dynamic query as shown in Script 2. Running it produces the desired (1, 1) and (2, 2) from SELECT @iCount, @iOut; after the two iterations.
---- Script 2:
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = NULL;
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
The above is a very simple demonstration. In the real world, @i might participate complex calculations. For example, it might be an output parameter of a stored procedure such as @expected_rowcount of sp_table_validation which behaves differently depending on whether or not @expected_rowcount is NULL. Knowing the difference with and without the explicit initialization will help us achieve the desired results.
Here I will discuss a gotcha when using sp_executesql with output parameters and in a loop. Instead of initializing the output parameters in each iteration of the loop, the value from the previous iteration is kept. Let's look at this simple example:
---- Script 1: Output parameter is not initialization
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
In Script 1, we have a WHILE loop for 2 iterations. The simple dynamic query as defined in @vcSQL has two parameters @i and @j as indicated in the execution of sp_executesql , with @i the OUTPUT parameter and @j the normal input parameter.
In the first iteration, @iCount = 1 which is passed to parameter @j. @i starts as NULL and therefore is assigned @j = 1. So, SELECT @iCount, @iOut; produces (1, 1).
In the second iteration, @iCount = 2 which again is assigned to @j, if @i were initialized, then just as in the first iteration, @i would be assigned @j=1, and SELECT @iCount, @iOut; would produce (2, 2). But actually, the result is (2, 1). That shows that at the start of iteration 2, @i was not initialized to NULL, but kept its previous value of 1 from iteration 1.
To make sure the output parameter is initialized in each iteration, we need to explicitly assign it a NULL value at the beginning inside the dynamic query as shown in Script 2. Running it produces the desired (1, 1) and (2, 2) from SELECT @iCount, @iOut; after the two iterations.
---- Script 2:
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = NULL;
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;
The above is a very simple demonstration. In the real world, @i might participate complex calculations. For example, it might be an output parameter of a stored procedure such as @expected_rowcount of sp_table_validation which behaves differently depending on whether or not @expected_rowcount is NULL. Knowing the difference with and without the explicit initialization will help us achieve the desired results.
Thursday, February 28, 2008
Puzzle - Counterfeit Coins
SQL Server Magazine (February 2008) has the following puzzle:
- This puzzle is from Clifford Jensen. Suppose you have 10 stacks of coins, with 10 coins in each stack. One stack consists of 10 counterfeit coins and the other 9 stacks each consist of 10 legitimate coins. Each legitimate coin weighs exactly 1 gram. Each counterfeit coin weighs exactly 0.9 grams. You have a digital scale that’s graduated in tenths of grams. Using the scale to take only one reading, determine which stack has the 10 counterfeit coins. You can weigh any number of coins from any number of stacks, but must you weigh them all together (i.e., you can take only one reading from the scale).
It is obvious that the solution has to be one in which coins of all 10 stacks are used in some way. In order to distinguish one stack from another, we need to take different number of coins from different stacks. So one solution is
- Label the 10 stacks (arbitrarily) from 1 to 10
- Take n coins from stack n, where n = 1, 2, …, 10. Put them on the scale and take a reading.
- Focus only on the single decimal digit. If it is 9, then the counterfeit stack is stack number 1; 8: stack 2; 7: stack 3; 6: stack 4; 5: stack 5; 4: stack 6; 3: stack 7; 2: stack 8; 1: stack 9. So, if we let j be the decimal digit from the reading, then the stack with all counterfeit coins is stack number (10 - j).
Wednesday, February 20, 2008
Understanding SQL plan_generation_num
SQL Server 2005 provides two important dynamic views sys.dm_exec_cached_plans (P) and sys.dm_exec_query_stats (S) to help understand cached plans and their statistics. Joining on [plan_handle], P and S have a 1-to-0 relationship. P has a record for every compiled plan in the plan cache, and S can have a record for every query statement in the corresponding cached plan. S can be empty for a cached plan of cache object types "Parse Tree" or "CLR Compiled Proc" or for objects such as system stored procedures, ad hoc queries, prepared statements, etc. Since I am mainly interested in user stored procedures and their compilation/recompilation frequency, for our purposes, P and S have a 1-to-many relationship - a cached plan for a stored procedure in P has one or more corresponding recoreds in S, one for each query statement in the stored procedure.
There are a lot of interesting columns in P and S, especially in S, and here I will only discuss what I have learned about plan_generation_num in S. SQL Server 2005 treats the compiled plan for a stored procedure as an array of subplans, one for each query statement. If an individual subplan needs recompilation, it does so without causing the whole plan to recompile. In doing so, SQL Server increments the plan_generation_num on the subplan record to be 1 + MAX(plan_generation_num for all subplans). The general distribution of plan_generation_num among all subplans for a given plan is such that it has multiple of 1's and distinct numbers > 1. That is because all subplans start with 1 as their plan_generation_num. Appendix A is the query for learning plan_generation_num.
Based on the above discussion, the higher the maximum(plan_generation_num) for a cached plan, the more frequent the plan has been recompiled. Be aware that it is not the recompilation of the whole plan but rather of the individual subplans that is building up maximum(plan_generation_num).
Appendix A.
Following is a query to list all plan_generation_num for a stored procedure
---- Analze recompilation via.plan_generation_num
DECLARE @vcName VARCHAR(50);
SELECT @vcName = 'qp_Temp';
SELECT
P.plan_handle,
S.statement_start_offset, S.statement_end_offset,
S.plan_generation_num,
S.execution_count,
P.usecounts,
S.creation_time,
S.last_execution_time,
sqlSmt = SUBSTRING(T2.text, (S.statement_start_offset/2) + 1, ((CASE WHEN S.statement_end_offset > 0 THEN S.statement_end_offset ELSE DATALENGTH(T2.text) END - S.statement_start_offset)/2) + 1),
L.query_plan,
TL.query_plan,
obj = OBJECT_NAME(T.objectid),
T.objectid,
P.cacheobjtype,
P.objtype,
T.text
FROM sys.dm_exec_cached_plans P
INNER JOIN sys.dm_exec_query_stats S
ON P.plan_handle = S.plan_handle
OUTER APPLY sys.dm_exec_sql_text(P.plan_handle) T
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) L
OUTER APPLY sys.dm_exec_sql_text(S.sql_handle) T2
CROSS APPLY sys.dm_exec_text_query_plan (P.plan_handle, DEFAULT, DEFAULT) TL
WHERE P.cacheobjtype = 'Compiled Plan' AND
T.text NOT LIKE '%dm_exec_cached_plans%' AND
P.objtype = 'Proc' AND T.dbid = DB_ID()
AND OBJECT_NAME(T.objectid) = 'qp_Temp'
ORDER BY P.plan_handle, S.statement_start_offset, S.statement_end_offset
There are a lot of interesting columns in P and S, especially in S, and here I will only discuss what I have learned about plan_generation_num in S. SQL Server 2005 treats the compiled plan for a stored procedure as an array of subplans, one for each query statement. If an individual subplan needs recompilation, it does so without causing the whole plan to recompile. In doing so, SQL Server increments the plan_generation_num on the subplan record to be 1 + MAX(plan_generation_num for all subplans). The general distribution of plan_generation_num among all subplans for a given plan is such that it has multiple of 1's and distinct numbers > 1. That is because all subplans start with 1 as their plan_generation_num. Appendix A is the query for learning plan_generation_num.
Based on the above discussion, the higher the maximum(plan_generation_num) for a cached plan, the more frequent the plan has been recompiled. Be aware that it is not the recompilation of the whole plan but rather of the individual subplans that is building up maximum(plan_generation_num).
Appendix A.
Following is a query to list all plan_generation_num for a stored procedure
---- Analze recompilation via.plan_generation_num
DECLARE @vcName VARCHAR(50);
SELECT @vcName = 'qp_Temp';
SELECT
P.plan_handle,
S.statement_start_offset, S.statement_end_offset,
S.plan_generation_num,
S.execution_count,
P.usecounts,
S.creation_time,
S.last_execution_time,
sqlSmt = SUBSTRING(T2.text, (S.statement_start_offset/2) + 1, ((CASE WHEN S.statement_end_offset > 0 THEN S.statement_end_offset ELSE DATALENGTH(T2.text) END - S.statement_start_offset)/2) + 1),
L.query_plan,
TL.query_plan,
obj = OBJECT_NAME(T.objectid),
T.objectid,
P.cacheobjtype,
P.objtype,
T.text
FROM sys.dm_exec_cached_plans P
INNER JOIN sys.dm_exec_query_stats S
ON P.plan_handle = S.plan_handle
OUTER APPLY sys.dm_exec_sql_text(P.plan_handle) T
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) L
OUTER APPLY sys.dm_exec_sql_text(S.sql_handle) T2
CROSS APPLY sys.dm_exec_text_query_plan (P.plan_handle, DEFAULT, DEFAULT) TL
WHERE P.cacheobjtype = 'Compiled Plan' AND
T.text NOT LIKE '%dm_exec_cached_plans%' AND
P.objtype = 'Proc' AND T.dbid = DB_ID()
AND OBJECT_NAME(T.objectid) = 'qp_Temp'
ORDER BY P.plan_handle, S.statement_start_offset, S.statement_end_offset
Monday, February 11, 2008
Website Down
A client called about its website being down. Even a simple page requested would show a blank screen, but without any browser error. The website was ping-able with decent responses. The IP returned from the DNS was correct - I had to check the IP because the client just renewed the domain registration and I was afraid the renewal somehow caused the IP associated with the website to be changed. That was not the case. Just to be sure. I also got the confirmation by "View Source" in the browser which showed the correct site info.
I then reviewed the System event log and noticed an recent installation of Windows Security updates KB941644 and KB943485. I tried to call Microsoft Product Support Services at 1-866-PCSAFETY for free security updates support to find out if Microsoft had any knowledge, but got no answer. I searched the web and found some complaints related to the updates, and one particular about the proxy being messed up and the user having to uninstall the updates to fix the problem. My client did not use a proxy server but that message told me that the updates might have changed something on the web server to interfere with Internet browsing or even page rendering. I checked the web server's log and noticed 404 errors associated with the GET from external Ips - and interestingly, from Intranet Ips, browsing the web site had not problem. Another important piece of info was that from the web server, I could not browse www.msn.com, www.microsoft.com, etc - the display showed the same blank screen just as an Internet user would get trying to browse my client's website, even though I could browse other web sites such as www.google.com. With the above facts, I first uninstalled the updates, but that did not fix the problem. So, something else was changed. I then went to Internet Options and restored the default settings. Whala! Everything started to work again.
That was a very unfortunate experience. My client's production web site was down for quite some time just because Microsoft sent out the updates without any warning about the potential effects. In this case, my client did nothing wrong, and it just followed Microsoft's recommendation. I hope Microsoft will be more thorough in QA and provide better guidance about its future updates.
I then reviewed the System event log and noticed an recent installation of Windows Security updates KB941644 and KB943485. I tried to call Microsoft Product Support Services at 1-866-PCSAFETY for free security updates support to find out if Microsoft had any knowledge, but got no answer. I searched the web and found some complaints related to the updates, and one particular about the proxy being messed up and the user having to uninstall the updates to fix the problem. My client did not use a proxy server but that message told me that the updates might have changed something on the web server to interfere with Internet browsing or even page rendering. I checked the web server's log and noticed 404 errors associated with the GET from external Ips - and interestingly, from Intranet Ips, browsing the web site had not problem. Another important piece of info was that from the web server, I could not browse www.msn.com, www.microsoft.com, etc - the display showed the same blank screen just as an Internet user would get trying to browse my client's website, even though I could browse other web sites such as www.google.com. With the above facts, I first uninstalled the updates, but that did not fix the problem. So, something else was changed. I then went to Internet Options and restored the default settings. Whala! Everything started to work again.
That was a very unfortunate experience. My client's production web site was down for quite some time just because Microsoft sent out the updates without any warning about the potential effects. In this case, my client did nothing wrong, and it just followed Microsoft's recommendation. I hope Microsoft will be more thorough in QA and provide better guidance about its future updates.
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 <http://msdn2.microsoft.com/en-us/library/ms171585.aspx>
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":
ANSI_NULL_DFLT_ON
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
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:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
ARITHIGNORE
DISABLE_DEF_CNST_CHK
IMPLICIT_TRANSACTIONS
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).
"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 <http://msdn2.microsoft.com/en-us/library/ms171585.aspx>
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":
ANSI_NULL_DFLT_ON
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
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:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
ARITHIGNORE
DISABLE_DEF_CNST_CHK
IMPLICIT_TRANSACTIONS
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).
Subscribe to:
Posts (Atom)