|
Following is the list of tracing types you can set for diagnostic tracing. Tracing types control the type of information for which to generate tracing data. Each trace type requires a corresponding condition, as noted below. Trace type values are stored in the trace_type column of the dbo.sa_diagnostic_tracing_level diagnostic table, and may have corresponding 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 |
Periodically collects a sample of frequently changing database and server statistics at regular intervals. Scopes and conditions: This trace type requires the DATABASE scope, and the SAMPLE_EVERY condition. |
| NONVOLATILE_STATISTICS |
Periodically 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 trace type requires the DATABASE scope, and takes the SAMPLE_EVERY condition. |
| CONNECTION_STATISTICS |
Periodically 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 trace type can be used with the DATABASE, USER, CONNECTION_NUMBER, and CONNECTION_NAME scopes, and takes the SAMPLE_EVERY condition. |
| 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 trace type can be used with all of the scopes, and takes any one of the following conditions: NONE, NULL, SAMPLE_EVERY. |
| PLANS |
Collects execution plans for queries, depending on the condition and scope. Scopes and conditions: This trace type can be used with all of the scopes, and takes any one of the following conditions: NONE, NULL, SAMPLE_EVERY, and ABSOLUTE_COST. |
| 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 trace type can be used with all of the scopes, and takes any one of the conditions. |
| STATEMENTS |
Collects SQL statements for the specified scope and condition. Internal variables are collected the first time each procedure is executed. This trace type is automatically included if the STATEMENTS_WITH_VARIABLES, PLANS, PLANS_WITH_STATISTICS, OPTIMIZATION_LOGGING, or OPTIMIZATION_LOGGING_WITH_PLANS tracing type is specified. Scopes and conditions: This trace type can be used with all of the scopes, and takes any one of the 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 trace type can be used with all of the scopes, and takes any one of the 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 trace type is automatically added whenever the OPTIMIZATION_LOGGING_WITH_PLANS trace type is specified. This trace type corresponds to all of the scopes, and does not take a condition. |
| 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 trace type is automatically added whenever the OPTIMIZATION_LOGGING_WITH_PLANS trace type is specified. This trace type corresponds to all of the scopes, and does not take a condition. |