in

SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

SQL Server 2008 performance data collection in 5 minutes

This will be a quick and dirty post on data collection in SQL Server 2008. The next two posts will cover creating custom data collection sets and integrating reporting services. For a deeper understanding of data collections, check out Bill Ramos's web cast. He talks about it in this post.

Data collection is basically a separate application that collects data about your SQL Server 2008 server, stores in SQL Server and provides reporting. It is like some of those 3rd party applications by vendors like Quest and Idera. It is fairly simple in design. This will allow you to easily get up to speed and extend. That is where it may excel over the 3rd party products.

The moving parts include an executable called dcexe.exe, a warehouse database, SSIS, SQL Agent jobs and your data sources. The sources can be perfmon counters, DMV's,SQL traces and even application data. It was designed to minimize the stress on the source system but the warehouse database should reside on a different system.

This feature comes with 3(as of right now) system collections.

  • Disk usage - this one basically logs the info from a sp_spaceused for each db. They say that disk space related performance counters may end up in here as well.
  • Query Statistics - This is "notable" queries from the procedure cache.
  • Server Activity - This collects data from system and OS perform counters as well as the SQLOS DMV's.

For a deeper discussion of this topic, please check out the web cast. The next post will demo setting up a custom collection. Part 3 will be on reporting off of the data collection.

 

Technorati Tags:

 

* Cross posted from http://statisticsio.com *

Comments

 

Canadian IT Professionals said:

Session 1 - Take Control of the Enterprise: Effective Solutions for Governing your Environment with Policy-Based

December 19, 2008 5:26 AM

About JasonMassie

Jason is a SQL Server Consultant for the professional services organization of Terremark (Formerly Data Return LLC) where he has worked for the last 8 1/2 years. Jason is an MCITP Database Administrator\Database Developer as well as an MCDBA on 7.0 and 2000. You can read his blog at http://statisticsio.com. He lives in Irving, TX. He enjoys time with his wife and three daughters as well as making electronic music. He can be reached at http://linkedin.com/in/jasonmassie or jmassie@terremark.com
Powered by Community Server (Commercial Edition), by Telligent Systems