SQl server data collection feature

SQL SERver 2008 has one more nice feature: data collection feature. You can create trace profile and collect data about those collections. It manages caching ,collection cycle and all others. They created most of the options inside the management studio. You don’t need to know detailed scripts. It got some detailed steps to start. First, you need to create data warehouse db for that. It is a very nice feature to track servers.  You can create your own collections and custom reports. Creating a  collection is not in that menu. You need to use script or use sql profiler to create a collection script. See more here http://msdn.microsoft.com/en-us/library/bb677356.aspx

Here is one script to create your own data collection after going through some setup. Before running this script, read about how to setup data collection.http://msdn.microsoft.com/en-us/library/bb677179(v=sql.105).aspx:

USE msdb;

DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier

EXEC dbo.sp_syscollector_create_collection_set
    @name = N'Custom query test1',
    @collection_mode = 0,
    @description = N'This is a test collection set',
    @logging_level=0,
    @days_until_expiration = 14,
    @schedule_name=N'CollectorSchedule_Every_15min',
    @collection_set_id = @collection_set_id OUTPUT,
    @collection_set_uid = @collection_set_uid OUTPUT
SELECT @collection_set_id,@collection_set_uid

DECLARE @collector_type_uid uniqueidentifier
SELECT @collector_type_uid = collector_type_uid FROM syscollector_collector_types 
WHERE name = N'Generic T-SQL Query Collector Type';

DECLARE @collection_item_id int
EXEC sp_syscollector_create_collection_item
@name= N'Custom query test1-item',
@parameters=N'
<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
  <Value>select * from sys.dm_exec_query_stats</Value>
  <OutputTable>dm_exec_query_stats</OutputTable>
</Query>
 </ns:TSQLQueryCollector>',
    @collection_item_id = @collection_item_id OUTPUT,
    @frequency = 5, -- This parameter is ignored in cached mode
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid
SELECT @collection_item_id
   
GO

You can connect data sources to your reports inside BI studio. Their BI studio has also very nice gadgets for reports.