Data Collectors

The Management Data Warehouse provides administrators with a simple mechanism to track statistics over time. By implementing the Management Data Warehouse, administrators can monitor performance and do trend analysis for the SQL Server 2008 R2 instances they manage.

The Management Data Warehouse is a relational database inside the SQL Server 2008 R2 instance that holds a variety of performance-related statistics. The performance statistics in the Management Data Warehouse are gathered via special data-gathering routines, known as data collections. The Management Data Warehouse can include data collection information from a sole instance or can alternatively hold data collected from multiple instances. The data collection process depends on prebuilt SSIS routines and SQL Server Agent jobs, which diminishes the number of tasks administrators need to do to build and maintain a database that contains performance statistics.

SQL Server 2008 R2 provides the following system data collection definitions:

• Disk usage

• Query activity

Server Activity

Each of these data collection definitions identifies the data to be collected, how often it should be collected, and how long it should be kept in the Management Data Warehouse.

Data collections can be run manually, on a schedule, or continually. Manual and scheduled data collections collect and upload data into the Management Data Warehouse on the same schedule. These types of data collections are known as noncached collections. When a data collection runs continually, data is cached in a directory and then uploaded to the Management Data Warehouse from time to time. These are known as cached collections.