The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational database known as the management data warehouse.
The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.
The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods for your data.
The data collector supports dynamic tuning for data collection and is extensible through its API.
The data collector provides predefined collector types that you can use for data collection. The collector types provide the actual mechanism for collecting data and uploading it to the management data warehouse.
1. Generic T-SQL Query Collector Type
The Generic T-SQL Query collector type executes a user-provided Transact-SQL statement as an input parameter, saves the output from the query, and then uploads the output to the management data warehouse. This collector type is registered in the core.supportedcollectortypes view in the management data warehouse.
This collector has the following input parameters:
Value - The Transact-SQL query. You can provide more than one query as input.
OutputTable - Provide the name of the table for saving query results before they are uploaded to the management data warehouse.
Database - Specify the database or databases to run the query against. You can specify databases by name or use the * wildcard character to specify all the databases on the server. If you do not provide a value for Database, the query is run against all the system databases.
2. 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.supportedcollectortypes 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 fntracegettable() 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.
3. Performance Counters Collector Type
You can use the Performance Counters collector type to collect specific performance counter information from a computer that is running SQL Server 2008. You can use this data later as a baseline for troubleshooting or for capacity planning. This collector type is registered in the core.supportedcollectortypes view.
This collector type has the following input parameters:
Objects. The SQL Server objects running in an instance of SQL Server.
Counters. The counters associated with a SQL Server object.
Instances. The instances of the specified object.
4. Query Activity Collector Type
The Query Activity collector type is a custom collector type used by the Query Statistics collection set, one of the predefined system data collection sets.
This collector type is used to collect query statistics and query activity information along with the query plan and query text for queries that meet predefined criteria. If you create your own collection set using this collector type, it will collect the same information as the Query Statistics collection set. Therefore, we recommend that you use the predefined Query Statistics collection set.