Setting Trace Level

You can use the TRACE_LEVEL parameter to manage the verbosity of event trace data stored in the event table.

TRACE_LEVEL is both an objects parameter and a sessions parameter, which means that you can set the parameter on objects and sessions.

Understanding Trace Levels

Through the TRACE_LEVEL parameter, you can specify the scope of trace events stored in the event table. You can set the TRACE_LEVEL parameter to one of the following values:

OFF:

Record no spans or trace events.

ALWAYS:

Record all spans and trace events.

ON_EVENT:

Record trace events only when your stored procedures or UDFs explicitly add events.

Setting the Trace Level on an Object

You can set the trace level on the following objects:

  • A stored procedure

  • A user-defined function (UDF)

  • A database or schema containing stored procedures and UDFs

To set the trace level on an object, do the following:

  1. Verify that you have the privilege to set the trace level on the object.

    You must use a role that is granted or inherits the following privileges:

    • The MODIFY TRACE LEVEL global privilege on the account

    • The MODIFY privilege on the object for which you want to set TRACE_LEVEL

    For example, to grant the central_event_admin role the privilege to set the trace level on any database, schema, stored procedure, or UDF in the account (even those for which the central_event_admin role has no other privileges), execute the following statement:

    GRANT MODIFY TRACE LEVEL ON ACCOUNT TO ROLE central_event_admin;
    
    Copy

    Note that in order to set the trace level on a stored procedure or UDF, the role must also have the USAGE privilege on the database and schema containing the stored procedure or UDF.

  2. Use the ALTER <object> command to set the TRACE_LEVEL parameter on the object.

    For example, to set the trace level for a specific UDF, use ALTER FUNCTION to set the TRACE_LEVEL parameter for that UDF. As another example, to set the default trace level for all functions and procedures in a database, use ALTER DATABASE to set the TRACE_LEVEL parameter on that database.

    The following example sets the trace level to ON_EVENT for all functions and procedures in the database db1. The example then overrides the trace level to ALWAYS for the UDF f1(int).

    USE ROLE central_event_admin;
    
    ALTER DATABASE db1 SET TRACE_LEVEL = ON_EVENT;
    ALTER FUNCTION f1(int) SET TRACE_LEVEL = ALWAYS;
    
    Copy

    For details on how Snowflake determines the effective trace level when the TRACE_LEVEL is set on different objects, see Understanding How Snowflake Determines the Effective Trace Level.

Setting the Trace Level for the Current Session

To set the trace level for calls to functions and procedures made in the current session:

  1. Verify that you have the privilege to set the trace level on the object.

    You must use a role that is granted or inherits the MODIFY SESSION TRACE LEVEL global privilege.

    For example, to grant the developer_debugging role the privilege to set the trace levels for the current session, execute the following statement:

    GRANT MODIFY SESSION TRACE LEVEL ON ACCOUNT TO ROLE developer_debugging;
    
    Copy
  2. Use the ALTER SESSION command to set the TRACE_LEVEL parameter for the current session.

    USE ROLE developer_debugging;
    
    ALTER SESSION SET TRACE_LEVEL = ALWAYS;
    
    Copy

If the TRACE_LEVEL parameter is set to different levels for the current session and on the functions and procedures called in that session, Snowflake determines the effective trace level to use. See Understanding How Snowflake Determines the Effective Trace Level.

Understanding How Snowflake Determines the Effective Trace Level

TRACE_LEVEL is both an object parameter and a session parameter.

Both the session parameter and object parameter types have a hierarchy of levels that allows you to override a parameter that is set at a higher level:

  • For session parameters, the hierarchy is Account » User » Session.

    This means that you can set the parameter for an account, override the account-level parameter for a user, and override the user-level parameter for the current session.

  • For object parameters, the hierarchy is Account » Database » Schema » Object.

    This means that you can set the parameter for an account, override the account-level parameter for a database or schema, and override the database- or schema-level parameter for specific stored procedures and UDFs in that database or schema.

For example, the TRACE_LEVEL for a function overrides the TRACE_LEVEL for the account that contains the function. If the TRACE_LEVEL for the account is ON_EVENT and the TRACE_LEVEL for a UDF in the account is ALWAYS, the effective TRACE_LEVEL is ALWAYS (the level for the function, not the account).

In the following example, the ALWAYS trace level is used because the function MY_UDF is lower than the ACCOUNT in the hierarchy.

ALTER ACCOUNT SET TRACE_LEVEL = ON_EVENT;

ALTER FUNCTION MY_UDF SET TRACE_LEVEL = ALWAYS;
Copy

In cases where TRACE_LEVEL is set in both the session and object parameter hierarchies, the most verbose TRACE_LEVEL is used. In other words, ALWAYS overrides ON_EVENT and OFF; ON_EVENT overrides OFF.

In the following example, the ALWAYS trace level is used because ALWAYS is more verbose than OFF.

ALTER SESSION SET TRACE_LEVEL = ALWAYS;

ALTER FUNCTION MY_UDF SET TRACE_LEVEL = OFF;
Copy