@@ OPTIONS bit mask versus DISABLE_DEF_CNST_CHK
We are trying to diagnose some performance issues on both SQL Server 2008 and SQL Server 2008 R2 arising from bad query plans that are cached on behalf of the users, but cannot accurately replicate them in SSMS as we cannot convince SQL Server to matches the application's set_options value of 255, which is returned from sys.dm_exec_plan_attributes, otherwise. @@ OPTIONS.
The @@ OPTIONS bit mask is documented on the following MSDN page: Configure User Options Server Configuration Option
According to the above page, the following combination of SET statements should yield @@ OPTIONS 255:
SET DISABLE_DEF_CNST_CHK ON
SET IMPLICIT_TRANSACTIONS ON
SET CURSOR_CLOSE_ON_COMMIT ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET ARITHIGNORE ON
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT OFF
SET ANSI_NULL_DFLT_ON OFF
SET ANSI_NULL_DFLT_OFF OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NUMERIC_ROUNDABORT OFF
SET XACT_ABORT OFF
But when you do this, you get a warning:
Line 1: The option 'DISABLE_DEF_CNST_CHK' is obsolete and has no effect.
And it PRINT @@OPTIONS
returns 254 instead of 255.
Clearly, Connection Pooling can sort this when you see it EXEC sp_reset_connection
in SQL Profiler, since none of our application code actually changes any SET parameters. But of course we cannot call sp_reset_connection from SSMS:
Msg 208, Level 16, State 9, Procedure sp_reset_connection, Line 1
Invalid object name 'sp_reset_connection'.
Is there a trick to insert the last bit of DISABLE_DEF_CNST_CHK into the game? Alternative parameter name or system table to configure?
source to share
Well, I found one way to set DISABLE_DEF_CNST_CHK, but I do NOT recommend doing this on a production server ...
When new connections are established, SQL Server sets @@ OPTIONS to the value stored in the User Options string in the sys.configurations view. The default is 0. You can check the configured and running value with:
select * from sys.configurations where name = 'user options'
-- or:
EXEC sp_configure 'user options'
As a user with the role sysadmin or serveradmin, you can change the value for future connections with:
EXEC sp_configure 'user options', 1
GO
RECONFIGURE
GO
Note that changing this setting affects all future connections to the server, so I do NOT recommend doing this on a production server.
After changing this config value, opening a new connection in SSMS using different SET options as described in the original question, we finally got to @@ OPTIONS 255.
source to share