sa_diagnostic_tracing

sa_diagnostic_tracing_level table

The sa_diagnostic_tracing_level table is owned by the dbo user, and each row in this table is a condition that determines what kind of diagnostic information to send to the tracing database. If a piece of logging data meets the conditions of one or more rows in this table, then the corresponding data is logged.

Data in this table is populated using the CONNECT TRACING or REFRESH TRACING LEVELS statements.

Columns

Column name	Column type	Column constraint	Table constraints
id	UNSIGNED INT	NOT NULL	Primary key.
scope	CHAR(32)	NOT NULL
identifier	CHAR(128)
trace_type	CHAR(32)	NOT NULL
trace_condition	CHAR(32)
value	UNSIGNED INT
enabled	BIT	NOT NULL

scope The scope of the diagnostic tracing, as listed below. To see the description for each scope, see Diagnostic tracing scopes.

DATABASE
ORIGIN
USER
CONNECTION_NAME
CONNECTION_NUMBER
FUNCTION
PROCEDURE
EVENT
TRIGGER
TABLE

id For internal use only.

identifier The identifier for the scope. This value changes, depending on the specified scope. For example:

if scope is DATABASE, identifier may not be present.
if scope is ORIGIN, identifier must be either Internal or External.
if scope is USER, identifier is the ID of the user.
if scope is CONNECTION_NAME, or CONNECTION_NUMBER, identifier is the name or number, respectively, for the connection.
if scope is FUNCTION, PROCEDURE, EVENT, TRIGGER, or TABLE, identifier is the fully qualified identifier for the object.

trace_type The type of data to trace for the specified scope, as listed below. To see the description for each trace type, see Diagnostic tracing types.

VOLATILE_STATISTICS
NONVOLATILE_STATISTICS
CONNECTION_STATISTICS
BLOCKING
PLANS
PLANS_WITH_STATISTICS
STATEMENTS
STATEMENTS_WITH_VARIABLES
OPTIMIZATION_LOGGING
OPTIMIZATION_LOGGING_WITH_PLANS

condition Applies only to plans, and controls whether to trace large, expensive queries, or queries for which the optimizer did not make optimal choices. Possible values are listed below. To see a description of each condition, see Diagnostic tracing conditions.

NONE, or NULL
SAMPLE_EVERY
ABSOLUTE_COST
RELATIVE_COST_DIFFERENCE

condition_value The value associated with the condition. For example, if condition is SAMPLE_EVERY, the condition_value would be a positive integer reflecting time in milliseconds. Additional rules are as follows:

If condition is NULL or NONE, there is no condition_value.
If condition is ABSOLUTE_COST, condition_value reflects the total actual cost of executing the statement, in milliseconds.
If condition is RELATIVE_COST_DIFFERENCE, condition_value reflects the cost of executing, as a percentage of the estimated cost.

enabled Whether the row is enabled. That is, whether the tracing settings in the row are active. 1 is enabled; 0 is disabled.

sa_diagnostic_tracing_level table

Columns

See also