Data Collector, Management Data Warehouse, Perfmon

Data Collector is a feature introduced in SQL Server 2008. A nice technology to monitor and report the performance of SQL Server systems. We can use Data Collectors in several different ways.

You can monitor using Perfmon at “t” moment or create a baseline using “Data Collector Set”.

Baseline creation can usually be useful to detect the problem when there is a problem in the system.

For example, you have created a data collector set for the Buffer Cache Hit Ratio(Indicates how much of the queries are read from the buffer cache. You can find the details of the counter in the table at the end of the article.)

And there was a problem in the database at “t” moment.

You have checked the Buffer Cache Hit Ratio ratio from “Data Collector Set” at “t” and you saw that the rate fell to 70% at the time of the problem. This means that there is an insufficiency of memory at the time of the problem. In this way, the baselines you create with data collectors can help you to solve the problem.

You should read the article “How To Calculate IOPS and Throughput of Your Database Server Using Data Collector” to monitor the counter counts from Perfmon and create baseline with data collector sets.

Another use of Data Collectors is the Management Data Warehouse. With Management Data Warehouse, we can create a baseline via SSMS using data collectors. But in its default configuration, baselines for CPU, Disk, Memory and Network are created. It is also possible to create custom baseline but I can say that it is not easy.

If only the Disk and Cpu monitoring is enough for you, I recommend you to use Utility Control Point.

Below you will find articles about Utility Control Point.

“How To Monitor SQL Server With Utility Control Point“,

“How To Manage Utility Control Point“,

“How To Add an Instance To The Utility Control Point”

Let’s examine the installation of Management Data Warehouse with an example.

From the Management tab on SSMS, right click on Data Collection and click Tasks and Configure Management Data Warehouse.

In the screen that appears, we are moving forward by selecting “Do not show this page again”

Management Data Warehouse needs a database to store the information it collects. On the next screen, it wants us to identify the database where it will save the information. We can select an existing database or we can create a new database for this process.

The next screen asks the user we want to authorize for the Management Data Warehouse. I’ve given admin rights to the TestLogin user. You can create a new login by clicking the New Login button.

The mdw_reader authorization is required for reporting, the mdw_writer authority is required to upload data, and the mdw_admin authorization is required to do both.

Next, click on Configure Data Collection as below to create our Data Collection Sets.

On the screen that appears,

In the Server name section, we select the instance of our MDW database that was created in the previous step.

In the database name section, we select our MDW database.

We need to select the file to keep before the data is transferred to the MDW database for Cache directory.

I created this folder on the C disk by creating a folder named Temp.

At the bottom, it asks for the data collector sets we want to activate. We select System Data Collection Sets and Transaction Performance Collection Sets and complete the process by clicking finish on the last screen.

You can find reports from the Management> Data Collection> Reports> Management Data Warehouse tabs as follows.

In addition to the Management Data Warehouse, you can create a baseline with data collector sets using the counter you want, as described in the article “How To Calculate IOPS and Throughput of Your Database Server Using Data Collector“.

Below you can find some important counter and best practice.