SET OPTIONS question

Question

Post reply

SET OPTIONS question

vsamantha35

SSChampion

Points: 11529
More actions
November 8, 2019 at 10:44 am
Go to Answer
#3696215

Hi All,
I am trying to get the SET OPTIONS using plan handle. I am using below dmv , but I get below output.
select * from sys.dm_exec_plan_attributes.(0x05000A00F2B6F25BA0FC923A2001000001000000000000000000000000000000000000000000000000000000)
How to get what values have been set ? I mean using value = 4347 how can get output something like below..
ANSI_WARNINGS :ON
ANSI_PADDING :ON
ANSI_NULLS :ON
ARITHABORT :ON
QUOTED_IDENTIFIER :ON
NOCOUNT :OFF
CONCAT_NULL_YIELDS_NULL :ON
NUMERIC_ROUNDABORT :OFF
XACT_ABORT :OFF

Also, can we get all the Set options for a SPID? I tried but I don't see any option. If any EVENT/ACTION is there, please let me know.
Below is the code I am using for creating Xevent session.
CREATE EVENT SESSION xe_trace_sqlstmts ON SERVER
ADD EVENT sqlserver.sql_batch_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb')),
ADD EVENT sqlserver.sql_statement_recompile(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb')),
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb')), ---exec
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb')),
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb'))
ADD TARGET package0.event_file(SET filename=N'C:\xevents\xe_trace_sqlstmts.xel',max_file_size=(1024))
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF
,STARTUP_STATE=OFF
)
GO
--start the traces
ALTER EVENT SESSION xe_trace_sqlstmts
ON SERVER
STATE = START;
GO
--start the traces
ALTER EVENT SESSION xe_trace_sqlstmts
ON SERVER
STATE = STOP;
GO
Thanks,
Sam

DesNorton

SSC-Insane

Points: 24274

November 8, 2019 at 2:02 pm

Answer

#3696248

If you have the value of the SetOptions, this will identify what options are ON|OFF

DECLARE @options int = 4347;

SELECT so.SetOption
     , OptionState = CASE WHEN so.SetValue & @options = so.SetValue THEN 'ON' ELSE 'OFF' END
FROM (VALUES
              ( CAST(1 AS int), 'DISABLE_DEF_CNST_CHK' )
            , (              2, 'IMPLICIT_TRANSACTIONS' )
            , (              4, 'CURSOR_CLOSE_ON_COMMIT' )
            , (              8, 'ANSI_WARNINGS' )
            , (             16, 'ANSI_PADDING' )
            , (             32, 'ANSI_NULLS' )
            , (             64, 'ARITHABORT' )
            , (            128, 'ARITHIGNORE' )
            , (            256, 'QUOTED_IDENTIFIER' )
            , (            512, 'NOCOUNT' )
            , (           1024, 'ANSI_NULL_DFLT_ON' )
            , (           2048, 'ANSI_NULL_DFLT_OFF' )
            , (           4096, 'CONCAT_NULL_YIELDS_NULL' )
            , (           8192, 'NUMERIC_ROUNDABORT' )
            , (          16384, 'XACT_ABORT' )
        ) AS so(SetValue, SetOption);

How to post data/code on a forum to get the best help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It tells you how to evaluate the SET options in the documentation for dm_exec_plan_attributes. You can use the bitwise operators to do it programmatically.

I don't know whether there's anything in Extended Events that will capture the SET options for you, but you could capture the plan handle and CROSS APPLY it to dm_exec_plan_attributes.

John

vsamantha35 SSChampion Points: 11529 More actions · Answer 2

vsamantha35

SSChampion

Points: 11529

November 8, 2019 at 11:54 am

#3696222

Okay. Thank you.

vsamantha35 SSChampion Points: 11529 More actions · Answer 3

vsamantha35

SSChampion

Points: 11529

November 8, 2019 at 2:35 pm

#3696252

Thanks a Ton 🙂