SQL Server >> SetOpts value in sys.syscacheobjects
When I filter for just ObjType = "proc" in sys.syscacheobjects I return the
following SetOpts:
155
171
187
235
251
258
763
I have searched BOL, and see that the SetOpts options include the following:
ANSI_PADDING
FORCEPLAN
CONCAT_NULL_YIELDS_NULL
ANSI_WARNINGS
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
ANSI_NULL_DFLT_OFF
How do I translate the values (numerics) returned by SetOpts in sys.
syscacheobjects to the text options listed in BOL?
--
Message posted via SQLMonster.com
http://www.hide-link.com/
> When I filter for just ObjType = "proc" in sys.syscacheobjects I return the
> following SetOpts:
> 155
> 171
> 187
> 235
> 251
> 258
> 763
>
> I have searched BOL, and see that the SetOpts options include the following:
> ANSI_PADDING
> FORCEPLAN
> CONCAT_NULL_YIELDS_NULL
> ANSI_WARNINGS
> ANSI_NULLS
> QUOTED_IDENTIFIER
> ANSI_NULL_DFLT_ON
> ANSI_NULL_DFLT_OFF
>
> How do I translate the values (numerics) returned by SetOpts in sys.
> syscacheobjects to the text options listed in BOL?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>
SQL Server >> SetOpts value in sys.syscacheobjects
> How do I translate the values (numerics) returned by SetOpts in sys.
> syscacheobjects to the text options listed in BOL?
The process for evaluating the Set Options value is described in the most
recent update (Feb 2007) to BOL in the topic sys.dm_exec_plan_attributes
(http://msdn2.microsoft.com/en-us/library/ms189472.aspx). This system
function is the replacement for sys.syscacheobjects in SQL Server 2005. You
can use the same process described in that topic for the SetOpts column in
sys.syscachobjects, or you can alter your query to use the new DMV. There's
an example in the DMV topic that returns the set options that might be
useful to you.
> When I filter for just ObjType = "proc" in sys.syscacheobjects I return
> the
> following SetOpts:
> 155
> 171
> 187
> 235
> 251
> 258
> 763
>
> I have searched BOL, and see that the SetOpts options include the
> following:
> ANSI_PADDING
> FORCEPLAN
> CONCAT_NULL_YIELDS_NULL
> ANSI_WARNINGS
> ANSI_NULLS
> QUOTED_IDENTIFIER
> ANSI_NULL_DFLT_ON
> ANSI_NULL_DFLT_OFF
>
> How do I translate the values (numerics) returned by SetOpts in sys.
> syscacheobjects to the text options listed in BOL?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>
SQL Server >> SetOpts value in sys.syscacheobjects
Thank you Gail and Tibor.
When I execute against sys.syscacheobjects or sys.dm_exec_plan_attributes, I
am not seeing a procedure that is executed regularly. Any idea why a compiled
plan of this procedure would not be cached?
>> How do I translate the values (numerics) returned by SetOpts in sys.
>> syscacheobjects to the text options listed in BOL?
>
>The process for evaluating the Set Options value is described in the most
>recent update (Feb 2007) to BOL in the topic sys.dm_exec_plan_attributes
>(http://msdn2.microsoft.com/en-us/library/ms189472.aspx). This system
>function is the replacement for sys.syscacheobjects in SQL Server 2005. You
>can use the same process described in that topic for the SetOpts column in
>sys.syscachobjects, or you can alter your query to use the new DMV. There's
>an example in the DMV topic that returns the set options that might be
>useful to you.
>
>You can download the latest version of Books Online from
>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> When I filter for just ObjType = "proc" in sys.syscacheobjects I return
>> the
>[quoted text clipped - 20 lines]
>> How do I translate the values (numerics) returned by SetOpts in sys.
>> syscacheobjects to the text options listed in BOL?
> Thank you Gail and Tibor.
>
> When I execute against sys.syscacheobjects or sys.dm_exec_plan_attributes, I
> am not seeing a procedure that is executed regularly. Any idea why a compiled
> plan of this procedure would not be cached?
>
>>> How do I translate the values (numerics) returned by SetOpts in sys.
>>> syscacheobjects to the text options listed in BOL?
>>
>>The process for evaluating the Set Options value is described in the most
>>recent update (Feb 2007) to BOL in the topic sys.dm_exec_plan_attributes
>>(http://msdn2.microsoft.com/en-us/library/ms189472.aspx). This system
>>function is the replacement for sys.syscacheobjects in SQL Server 2005. You
>>can use the same process described in that topic for the SetOpts column in
>>sys.syscachobjects, or you can alter your query to use the new DMV. There's
>>an example in the DMV topic that returns the set options that might be
>>useful to you.
>>
>>You can download the latest version of Books Online from
>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>>> When I filter for just ObjType = "proc" in sys.syscacheobjects I return
>>> the
>>[quoted text clipped - 20 lines]
>>> How do I translate the values (numerics) returned by SetOpts in sys.
>>> syscacheobjects to the text options listed in BOL?
>
> --
> Message posted via http://www.sqlmonster.com
>