For those of you who have read my posts then you will know I am very big on Utility Databases. I like them because there is a plethora of information that can be gathered that you can trend, baseline, troubleshoot, etc.
In browsing some forums and whatnot I came across a good article regarding the data collector within SQL. I decided in some late night reading to study up on it; I found it to be something that in our line of business pretty beneficial. This component of SQL 2008 collects different data sets. I like it for many reasons; one perk that stood out to me was configuring the different types of monitors on a scheduled bases. You may ask what is the difference between this and the Utility Databases I always speak of. I am glad you asked; my Utility Databases are custom fitting to me to meet my exact needs; this data collector component comes out of the box you just have to configure it.
I’ve started utilizing both; configuring the data collector takes a matter of minutes and you can set schedules and retention periods of how long you want to keep your data. I basically set up an empty database shell; then the configuration takes care of creating the necessary stored procedures and schema to support it. Another feature that I liked are the 3 reports that come with it out of the box – disk usage, query stats history, and server stats history. Granted all these can be gathered via scripts but why not take advantage of something already built for you.
After research I found that this resides best on a CMS Server if you have one. If you are unfamiliar with the CMS Server check out John Sterret’s blog. I sat on one of his sessions at PASS and really enjoyed it.
If you aren’t collecting data your missing out; if you are collecting data and aren’t using the two methodologies take some time and review them; it might just meet your needs.