Article
10/01/2010

Generic SQL Trace Collector Type

The Generic SQL Trace collector type uses SQL Trace to monitor the SQL Server Relational Engine. The trace data can come from a default trace or from one or more custom traces. This collector type is registered in the core.supported_collector_types view.

The default trace, controlled by a server-wide setting, runs continuously on the server and captures general events of interest. These events of interest are not related to individual batch execution. This is a low-impact trace. Custom traces can collect any events and can have low or high impact, depending on which events are selected and the server activity at the time that the trace is running. In most cases, custom traces do not run continuously.

The Generic SQL Trace collector type runs a server-side trace that stores data in a file or set of files. The trace data is obtained from trace files by using the fn_trace_gettable() system function. If configured to do so, the collector processes the data and then uploads the data to the management data warehouse.

The Generic SQL Trace collector type is configured to remove unused files and to keep a fixed amount of space for stored trace data.

Generic SQL Trace Input Schema

The Generic SQL Trace collector type uses the following schema for input parameters.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
  <xs:element name="SqlTraceCollector">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Events">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="EventType">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="Event">
                      <xs:complexType>
                        <xs:attribute name="id" type="xs:unsignedByte" use="required" />
                        <xs:attribute name="name" type="xs:string" use="required" />
                        <xs:attribute name="columnslist" type="xs:string" use="optional" />
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute name="id" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="name" type="xs:string" use="required" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Filters">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Filter" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:attribute name="columnid" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="columnname" type="xs:string" use="required" />
                  <xs:attribute name="logical_operator" type="xs:string" use="required" />
                  <xs:attribute name="comparison_operator" type="xs:string" use="required" />
                  <xs:attribute name="value" type="xs:string" use="required" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="use_default" type="xs:boolean" />
    </xs:complexType>
  </xs:element>
</xs:schema>

As shown in the schema, the collector type contains key elements, stored procedure parameters, and special columns.

Key elements

The input trace definition contains a list of events and a list of filters that define the trace.
Events are grouped within an EventType node, which corresponds to Event Category in SQL Server Profiler.
For the EventType node, the ID attribute must match the value from the sys.trace_category system view. The name attribute is optional.
For the Event node, the ID attribute must match the value from the sys.trace_events system view. The name attribute is optional.
IDs are used when generating trace start script. The names are used for readability and for rendering of input definition in the user interface.
Event node contains the following attributes:
- ID, name. Explained previously.
- columnslist. A comma-separated list of column IDs to be selected for the event. If columnslist is not specified, all the columns for this event will be included.
The Filter node defines a filter applied to the trace. The attributes have the following meanings:
- columnid. ID of a column that the filter applies to.
- columnname. The name of the column identified by columnid. This is used for rendering.
- logical_operator. An operator to be applied between multiple filters on the same column. Allowed values are AND and OR.
- comparison_operator. An operator to be applied between the column and the filter value. Allowed values are: EQ, NE, GT, GE, LT, LE, LIKE, and NOTLIKE.
- value. The value to use for comparison. Note that the value of the filter and the comparison operator must match the type of the column that the filter is applied to. For example, if the column type is string, only the "LIKE" and "NOTLIKE" operators can be used, and only string values are accepted for the filter value.

Stored procedure parameters

The following parameters of the sp_trace_create stored procedure are defined based on the collection set or the collector options.

@options. Trace is always started with the rollover option set (TRACE_FILE_ROLLOVER).
@tracefile. The location of trace files is determined by the CollectorTempDir variable. The name of the trace file is generated as a concatenation of: "DataCollector_" + instanceName + CollectionSetUid + CollectionItemId + ".trc".
@maxfilesize. Is always set to 5 megabytes (MB).
@stoptime. Not used.
@filecount. Set to fit the maximum temporary storage allowed per collection set (in MB). @filecount = storage limit/5.

Special columns

Additional columns are provided for each event, even if they are not preselected in the input parameters for that event. These columns are:

StartTime
EndTime
EventSequence
SPID

The preceding columns identify event origin and enable event correlation.

The following code example illustrates the use of the input schema used by the Generic SQL Trace collector type.

<?xml version="1.0" encoding="utf-8"?>

<ns:SqlTraceCollector xmlns:ns="DataCollectorType" normalize_sql="0" normalize_plans="0" normalize_procedures="0" normalize_connections="0" 
normalize_default="1">

<Events>
<EventType id ="6" name="Performance">
<Event id="58" name="Auto Stats"/>
<Event id="165" name="Performance statistics"/>
<Event id="146" name="Showplan XML Statistics Profile"/>
</EventType>
<EventType id="13" name="TSQL">
<Event id="12" name="SQL:BatchCompleted" columnslist="1, 3, 9, 10, 13, 16, 17, 18"/>
<Event id="13" name="SQL:BatchStarting"/>
<Event id="41" name="SQL:StmtCompleted"/>
<Event id="166" name="SQL:StmtRecompile"/>
</EventType>
<EventType id="20" name="CLR">
<Event id="196" name="Assembly Load"/>
</EventType>
<EventType id="1" name="Cursors">
<Event id="53" name="CursorOpen"/>
<Event id="75" name="CursorRecompile"/>
<Event id="76" name="CursorImplicitConversion"/>
<Event id="78" name="CursorClose"/>
</EventType>
</Events>

<Filters>
<Filter columnid="13" columnname="Duration" logical_operator="AND" comparison_operator="GE" value="1000L"/>
<Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="LIKE" value="Data Collector"/>
<Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="EQ" value="NULL"/>
<Filter columnid="18" columnname="CPU" logical_operator="AND" comparison_operator="EQ" value="20"/>
<Filter columnid="14" columnname="StartTime" logical_operator="AND" comparison_operator="GT" value="2007-02-09 13:40:00"/>
</Filters>

</ns:SqlTraceCollector>

Processing and Output

This version of the Generic SQL Trace collector type supports a full trace data load that processes events captured in the server default trace.

Full trace data load

With this type of data load the trace data is loaded without any processing into a single table that contains all the possible trace columns. Data from multiple traces can be loaded into the same table, which makes it easier to merge the data. In addition to the trace data, a snapshot_id is appended to each row, which makes it possible to identify the source of the trace data and the time of the trace.

The benefits that are provided by a full trace data load include the following:

There is a simple way to get trace data from the server to a database where it can be easily queried and further processed without having to open the trace in SQL Server Profiler.
Data from multiple traces can be merged and correlated together.
There is no loss of data from the original trace. Everything that was captured is retained.
Existing tools, such as SQL Server Profiler, can be used to browse through the data.

Destination Schema

The destination schema is defined as one table that captures details about the traces stored in the management data warehouse, and one table that is used to store all the trace events from the traces. The trace data is stored in the following management data warehouse tables:

snapshots.trace_info. This table contains information about all the traces that have been uploaded to the warehouse instance.
snapshots.trace_data. This table contains data captured by all the traces. It defines a column for each possible trace column. Storing trace data this way allows the data collector to insert data in the table in the same shape as when it comes out of the fn_trace_gettable() system function. It also allows the table to be loaded directly into SQL Server Profiler.

For more information about these tables, see The Management Data Warehouse.

Change History

Updated content
Corrected the Generic SQL Trace input schema.
Corrected the code example that shows the use of the input schema that is used by the Generic SQL Trace collector type.

Share via

Generic SQL Trace Collector Type

Generic SQL Trace Input Schema

Processing and Output

Destination Schema

See Also

Tasks

Concepts

Other Resources

Change History

Additional resources