SQL Server 2008 Data Collector

Versione in italiano


Introduction

The typical problems of any DBA is to monitor the installation of its DB in terms of performance and server loads.
The process of preparing data collectors in order to do an analysis usually is a time-consuming and tricky task.
But if we are Database SQL Server 2008 lucky DBA, Microsoft has developed a tool that does the work for us: SQL data collector (not to be confused with Windows performace monitor  Data Collector).
In this way, the data collectors become a powerful ally of all DBAs, especially those less experienced.
Architecture

Although the architecture is quite complex

Data Collector Architecture: source MSDN

Installation is very simple because the configuration and parameterization is minimal, however, the data collector was designed to have extremely low overhead on the system when collecting and analyzing data.
It creates SSIS Package (SQL Server Integration Service)  launched by SQL Server Agent Jobs working to collect and store data in a Management Data WareHouse  (from henceforth MDW ) and finally the information is shown through a series of dynamic reports linked to each other that allow us to analyze in detail our SQL Server instances activities and trends. 

Installation

Open SSMS and go to the section of Management, right-click and select a data collector configuration data collectors

Then proceed with the MDW configuration

Choose where you want to create it and how to call it (I've imaginatively called it DataCollector).

CAUTION: The data warehouse DB can come to occupy up to 10GB of disk space!

So be careful where you position it and its initial and maximum size (if you want to set it)

Going forward we are asked to configure roles and permissions, but if you don't have any special need you can proceed without selecting anything. So  you complete this first configuration phase

Again we return to the Configuration

And this time we choose to set the data collection

and simply select the previous data warehouse and choose a directory in which park the data collected before they are placed in the MDW (see below: Query and System collectors collect data with a high frequency, but uploading to the Data Wharehouse is deferred in ways to avoid too much overhead due a nearly continuous data entry)

Even here we can proceed and complete the configuration

At this time our collectors are finally activated and slowly begin to populate our MDW

Data Collectors

After configuring our SSIS packages and their jobs are ready and running, you'll see the collectors  properties below that are shown only for explanatory purposes, those who were not interested in learning more can safely skip this paragraph.

How we can inferred in the previous images we have three types of collectors (4 in SQL 2008 R2):

1. Disk Usage
2. Query Statistics 
3. Server Activity

 Disk Usage: keeps track of  Database and Log files growth collecting information from a series of system tables and views.
Sampling is done every 6 hours and is loaded simultaneously on the MDW (given the low frequency). The data is kept for 730 days so as to allow adequate delineation of the growth trends.

Query statistics: collects data on SQL statements and their execution plans sampling every 10 seconds some Dynamic Management Views ( from now  DMV). MDW loading in this case is delayed and occurs every 15 minutes. Collected data is stored in the MDW for 14 days.

Server Activity: provides a view on the SQL Server activity, resources use and competition over resources, and also collects data on overall System resources allowing to identify any bottlenecks external respect than DB engine. In this case the data are collected by querying DMVs and SQL Sever and system performance counters.
Sampling occurs every 60 seconds and the data is uploaded every 15 minutes in  the MDW where they are kept for 14 days.

As can be seen from the images is possible, but absolutely  not necessary, change any parameter, such as data retention period in the MDW.

Reports

If you do not want to wait for the default schedules at any time you can force data collection and loading .

Collected data  analysis will put a strain on even a very experienced DBA, and for this reason that the second, and perhaps the greatest, advantage of the Data Collector are available reports.

As you can see reports are related one to one with collectors.
So let's see how our data are available and their graphical representation.
I apologize for the fact that all pictures are taken from a test environment virtually stationary, where the only load is the Data Collector, but this must not lead to the error of thinking that the tool creates a strong overhead on monitored systems.
 Disk Usage

Show all of our instance databases data and log files in terms of size and growth. Clicking on the name of the individual DB we can see free space and a list of all events related to their growth.

Query Statistics

First displays Queries Top 10 in relation to their resource consumption (default CPU) in a graphical format

and in textual form

Clicking on a single statement you can display details with valuable informations about resources use and the possibility of going into Waits details.

Server Activity:

This report displays the graphs for the four SQL tuning key resources. Very interesting is the fact that allows us to separate SQL Server from System activity.
It also puts in a graphical format, with the ability to go into detail with a single click, SQL Server Waits and the most significant activities in terms of SQL Server monitoring.

Conclusions and final notes


As we have seen this tool combines two great qualities: ease of configuration and power of analisys, so I'm sure that it will soon become part of almost all of your SQL Server installations.
Unfortunately, SQL Server 2005 does not support data collectors but MSSQLDUDE suggests us a technique to remedy the problem (to be honest I have never tried it)
Also remember that you can always export charts to PDF or Excel format.

Link:
Data Collector on MSDN
SQL Authority (where I stole some pictures)