SQL SERVER – Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005

This article was written as a response to T-SQL Tuesday #005 – Reporting.

The three most important components of any computer and server are the CPU, Memory, and Hard disk specification. This post talks about  how to get more details about these three most important components using the Management Data Collection. Management Data Collection generates the reports for the three said components by default. Configuring Data Collection is a very easy task and can be done very quickly.

Please note:
There are many different ways to get reports generated for CPU, Memory and IO. You can use DMVs, Extended Events as well Perfmon to trace the data.
Keeping the T-SQL Tuesday subject of reporting this post is created to give visual tutorial to quickly configure Data Collection and generate Reports.

From Book On-Line:

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.

Let us go over the visual tutorial on how quickly Data Collection can be configured. Expand the management node under the main server node and follow the direction in the pictures.

This reports can be exported to PDF as well Excel by writing clicking on reports.

Now let us see more additional screenshots of the reports. The reports are very self-explanatory  but can be drilled down to get further details. Click on the image to make it larger.

Well, as we can see, it is very easy to configure and utilize this tool. Do you use this tool in your organization?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005

  1. Thanks a lot for the tutorial, simple but elaborative. It would be more helpful, if you can add any tutorial for “Change Data Capture”.

  2. My MDW kept growing at the pace of 4GB per week. Now the whole Data Collection DB is over 99 GB. I tried to shrink it and it did not work. Any suggestions?

  3. Pingback: SQL SERVER – Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video « SQL Server Journey with SQL Authority

  4. Can we filter the top 10 queries by database becuase it seems that top 10 queries are system generated and we cannot do performance tuning on them.

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

  6. I struggle to find any good reviews on this feature. Is anyone really using it? With the feature collecting data from DMVs\wait stats\perf counters that we commonly use for you automatically, and the SSRS drill down reports it uses, it seems crazy that I can’t find much on it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s