Data Collector in SQL Server

SQL Server 2008 introduced a new feature called "data collector".

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.

You can setup data collections on multiple SQL Servers and have all such data collections update 1 common database called the management data warehouse.

There are 2 types of data collection sets:   cached or non-cached.    Non-cached means the data collection and the upload to the management data warehouse occur at the same time.    Cached means the 2 processes happen at different times.

When you create a collection data set, you have to tell SQL Server what data to collect.   You can define your own (i.e. create a custom data collection set), or pick from one of 3 system data collector sets pre-built in SQL Server:
  • Disk useage collection set
  • Query statistics collection set
  • Server activity collection set