Diagnostic tracing types

Following is the list of types you can set choose from for diagnostic tracing. Diagnostic tracing types control the information for which to generate tracing data. Each diagnostic tracing type requires a corresponding condition, as noted below, and is stored in the trace_type column of the dbo.sa_diagnostic_tracing_level diagnostic table, and may have corresponding diagnostic tracing conditions, which are stored in the trace_condition column. For a list of all possible conditions, see Diagnostic tracing conditions.

The values in trace_type column reflect the settings specified in the Database Tracing Wizard.

Value in the trace_type column	Description
VOLATILE_STATISTICS	Collects a sample of frequently changing database and server statistics. Scopes and conditions: This diagnostic tracing type requires the DATABASE scope, and uses the SAMPLE_EVERY condition as the interval at which to collect the data. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
NONVOLATILE_STATISTICS	Collects a sample of database and server statistics that do not change frequently. Non-volatile statistics cannot be collected more frequently then volatile statistics. Volatile statistics must be collected in order for non-volatile statistics to be collected, and the time difference between the sampling for non-volatile statistics should be a multiple of the time difference specified for the volatile statistics. Scopes and conditions: This diagnostic tracing type requires the DATABASE scope, and uses the SAMPLE_EVERY condition as the interval at which to collect the data. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
CONNECTION_STATISTICS	Collects a sample of connection statistics. If the scope is database, statistics for all connections to the database are collected. If the scope is user, statistics for all connections for the specified user are collected. If the scope is CONNECTION_NAME or CONNECTION_NUMBER, only statistics for the specified connection are collected. Volatile statistics have to be collected in order for CONNECTION_STATISTICS to be collected, and the time interval between sampling should be a multiple of that specified for the VOLATILE_STATISTICS. Scopes and conditions: This diagnostic tracing type can be used with the DATABASE, USER, CONNECTION_NUMBER, and CONNECTION_NAME scopes, and uses the SAMPLE_EVERY condition as the interval at which to collect the data. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
BLOCKING	Collects information about blocks according to the specified scope and condition. If the scope is CONNECTION_NAME or CONNECTION_NUMBER, then the block may be recorded when the connection blocks another connection, or is blocked by another connection. Scopes and conditions: This diagnostic tracing type can be used with all of the scopes, and can use any one of the following conditions for collection: NONE, NULL, SAMPLE_EVERY. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
PLANS	Collects execution plans for queries, depending on the condition and scope. Scopes and conditions: This diagnostic tracing type can be used with all of the scopes, and can use any one of the following conditions for collection: NONE, NULL, SAMPLE_EVERY, and ABSOLUTE_COST. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
PLANS_WITH_STATISTICS	Collects plans with execution statistics. Plans are recorded at cursor close time. If the RELATIVE_COST_DIFFERENCE condition is specified, part of the statistics in the output might be best-guess statistics. Scopes and conditions: This diagnostic tracing type can be used with all of the scopes, and accepts any one of the conditions for collection.
STATEMENTS	Collects SQL statements for the specified scope and condition. Internal variables are collected the first time each procedure is executed. This diagnostic tracing type is automatically included if the STATEMENTS_WITH_VARIABLES, PLANS, PLANS_WITH_STATISTICS, OPTIMIZATION_LOGGING, or OPTIMIZATION_LOGGING_WITH_PLANS diagnostic tracing type is specified. Scopes and conditions: This diagnostic tracing type can be used with all of the scopes, and can use any one of the conditions for collection. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
STATEMENTS_WITH_VARIABLES	Collects SQL statements and the variables attached to the statements. For each variable, either internal or host, all the values that were assigned are collected as well. Scopes and conditions: This diagnostic tracing type can be used with all of the scopes, and can use any one of the conditions for collection. See Diagnostic tracing scopes, and Diagnostic tracing conditions.
OPTIMIZATION_LOGGING	Collects data about join strategies considered by the optimizer for execution of each query. Information about cost of execution of each strategy, as well as basic information necessary to reconstruct the tree for the structure, is collected. Information about rewrites applied to the query is also collected. If a scope other than DATABASE, CONNECTION_NAME, CONNECTION_NUMBER, ORIGIN, or USER is used, the first recorded statement text might be different than the initial text of the query since some rewrites can be applied before it can be determined that optimization logging should be applied to the current statement. This diagnostic tracing type is automatically added whenever the OPTIMIZATION_LOGGING_WITH_PLANS tracing type is specified. This diagnostic tracing type corresponds to all of the scopes, and does not take a condition. See Diagnostic tracing scopes.
OPTIMIZATION_LOGGING_WITH_PLANS	Collects data about join strategies considered by the optimizer. Information about the cost of execution for each strategy, as well as the complete XML plan describing the join strategy tree structure, is collected. Information about rewrites applied to the query is also collected. If a scope other then DATABASE, CONNECTION_NAME, CONNECTION_NUMBER, ORIGIN, or USER is used, the first recorded statement text might be different then the initial text of the query since some rewrites can be applied before it can be determined that optimization logging should be applied to the current statement. The OPTIMIZATION_LOGGING tracing type is automatically added whenever the OPTIMIZATION_LOGGING_WITH_PLANS tracing type is specified. This diagnostic tracing type corresponds to all of the scopes, and does not take a condition. See Diagnostic tracing scopes.

Diagnostic tracing types

See also