SQL Server 2008: The Data Collector

Based on CTP6 (Feb 08' build): Drop me a line and let me know your thoughts and experience with the new Data Collector subsystem!

Its nice to get a few minutes to post a blog, things have been busy (which is good) .  More to come on some other things in a bit...

I want to take a few minutes of your valuable time to give you a brief overview of the new SQL Server 2008 Data Collector (DC) subsystem. This post will be 1 of 2 on the topic of the Data Collecotr with the 2nd part serving as a more detailed tour of the subsystem. Like most new subsystems in SQL Server these days, the DC is composed of several 'lower' layers or platforms in the larger SQL Server product, namely SQL Server Agent and Integration Services (SSIS). So right of the bat, if you are not farily well educated in SQL Server Agent and SSIS you should brush up on those topics first before learning about DC.

Performance Data Warehouses (PDW) are not a new idea, however they are rarely implemented. Why? Because the amount of effort for creating a custom PDW (especially when its a 'pure' DBA doing it, no offense) far outweighs the return on investment or atleast that is the perception generally speaking. Microsoft has recognized this and added a new feature out-of-the-box to allow DBAs to create and populate their PDWs without the need to author complex SSIS/ETL packages or Reporting Services's (SSRS) reports. With just a few clicks in Management Studio (SSMS), one can quickly enable the Data Collector subsystem (disabled by default) and also start the default System Data Collection Sets. The DBA must also configure the Management Data Warehouse (MDW) which is essentially the DC's version of the PDW, that is it serves as the central relational repository for both system and custom Collection Sets from 1 or more target servers.

*DC is only supported on SQL Server 2008 based systems

The System Collection Sets leverage the all three supporte data sources. As the DC subsytem evolves, more data sources are expected to become available such as Windows Management Instrumentation (WMI) among others. When you create a Custom Collection Set you will have to specify which data sources or Collector Type to use. Currently, the DC supports three data sources:

  1. TSQL Queries
  2. SQL Trace
  3. PerfMon Objects/Counters

To create Custom Collection Sets you must leverage TSQL at the moment as SSMS does not support this. According to those at Microsoft, the DC should not (or is suppose to not) consume more than 5% total CPU time on 2008 systems and plan for around 250-350 MB per day of record activity. True activity measures are ofcourse central to each organization's usage but it does give you some ballpark figures to plan with and contemplate. The two key databases that drive the DC subsystem is the MSDB and the MDW. The MSDB contains all or most of the DC's metadata and configuration while the MDW contains (mostly) the actual data collected into 'snapshot' tables.

The DC is a great tool (and start) to enabling out-of-the-box performance data warehouses for SQL Server 2008 database systems. Within just a few minutes a user can configure an OLTP SQL Server 2008 system to collect and upload its collection set defined items to an designated MDW server. There were a few items mentioned in BOL that I could not locate and thus below are some of the items still needed (regardless of if they are in development or not):
1.       Dashboards (if not currently in the CTP6 build, cannot locate it in CTP6)
2.       Fact & Dimensions Tables/DMVs/Cubes (again, if not currently in the CTP6 build)
3.       Better integrated SSRS Reports in Management Studio including multi-server based ones
4.       Management Studio facilitating the creation of Custom Collection Sets (currently must use TSQL)
5.       Integration with the new policy framework of SQL Server 2008
6.       Performance Data Collection on the other SQL Server platforms: SSRS/SSIS/SSAS
7.       Continually consume less system resources per SQL Server build/product iteration

Resources

  1. http://blogs.msdn.com/sqlrem/archive/2008/01/17/performance-studio-for-sql-server-2008-revealed.aspx
  2. http://sqlblogcasts.com/blogs/jasonmassie/archive/2008/02/15/sql-server-2008-performance-data-collection-in-5-minutes.aspx
  3. http://sqlblogcasts.com/blogs/jasonmassie/archive/2008/02/15/creating-a-custom-data-collection-in-sql-server-2008.aspx
  4. SQL Server 2008 BOL

Cheers

Derek

Print | posted on Tuesday, February 26, 2008 11:50 PM

Feedback

# re: SQL Server 2008: The Data Collector

Left by Buchaiah at 11/11/2008 7:00 AM
Gravatar The information is slightly wrong because the number of data collectors are 4 but you have mentioned as only 3.

# re: SQL Server 2008: The Data Collector

Left by Lori at 10/9/2009 11:06 AM
Gravatar Do you have the 2nd part of this series? I'm not finding many articles on the net about people using this component successfully, even with it's pitfalls.

# re: SQL Server 2008: The Data Collector

Left by clay at 3/3/2010 5:12 PM
Gravatar your not mentioning how to enable it "in just a few clicks"

# re: SQL Server 2008: The Data Collector

Left by columbia jackets at 10/21/2010 4:19 PM
Gravatar that is it serves as the central relational repository for both system and custom Collection Sets from 1 or more target servers.

snow boots for women | columbia sportswear outlet | cheap mac makeup | the north face jackets

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets

# re: SQL Server 2008: The Data Collector

Left by Adidas Wing Shoes at 8/19/2011 9:31 PM
Gravatar js wings sneakers Each pair of Adicolor shoes sell with six different colors of watercolor pens.They do so to leave that to you to show your characteric ,Adidas Jeremy Scott 3 Tongue, and you can create freely,so everyone can has their unique shoes ,and this stands for freedom. This made an impact at that time and attrated many attention. wing shoes

# zdyabl

Left by xgtxskkp at 2/7/2012 2:58 PM
Gravatar 5BshPt npctatgfwgxr, [url=http://ssvkegfiqqhr.com/]ssvkegfiqqhr[/url], [link=http://wnfdxlxwopig.com/]wnfdxlxwopig[/link], http://zkragptewugj.com/

# cfieu

Left by Szwhhmdw at 2/9/2012 3:07 AM
Gravatar chanel payday - payday

# msogfkavr

Left by Eboizlmz at 2/25/2012 11:52 PM
Gravatar kruschev kopa isoptin sr - kopa isoptin sr

# ctnkiaswh

Left by Fficpwbh at 2/26/2012 6:35 AM
Gravatar walmartphoto acheter dulcolax - acheter dulcolax

# fnnthdeh

Left by Rkjcdtyy at 2/27/2012 12:21 AM
Gravatar hrc acquisto clozaril - acquisto clozaril

# re: SQL Server 2008: The Data Collector

Left by gjiqui at 9/13/2012 8:53 PM
Gravatar I'm not finding many articles on the net about people using this component successfully, even with it's pitfalls. simply click listed here
Comments have been closed on this topic.

Copyright © Derek Comingore

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski