SET OPTIONS question

  • vsamantha35

    SSChampion

    Points: 11077

    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)

    trace1

    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.

    trace2

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148360

    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: 11077

    Okay. Thank you.

  • DesNorton

    SSC-Insane

    Points: 22810

    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);
  • vsamantha35

    SSChampion

    Points: 11077

    Thanks a Ton 🙂

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

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