SQL Server 2008 - Data Collection

What is Data Collection in SSMS 2008?

Data Collection option in SSMS 2008 is used to gather data about Disk Usage,Query Statistics,Server Activity and generate report about 

1.)Disk Usage Summary:

disk space used by databases and

data file & log file growth rate of a database in a SQL Server 2008 instance.

2.)Query Statistics History:

execution count of a query,

total duration for a query execution,

I/O cost & CPU utilization of a query in a SQL Server 2008 instance.

3.)Server Activity History:

resource consumption details like CPU,memory,disk i/o & network usuage,

SQL Server waits,

SQL Server activities like (user connections,logins,logouts,transactions,batch requests and SQL compilations & recompilations) for a SQL Server 2008 instance and also for host OS where data collection option is configured.

Where  Data Collection in SSMS 2008 is located?

Object Explorer - > Management - > Data Collection

 Process involved  in Data Collection:

To be noted: 

1.)Data Collection option works only from SQL Server 2008 .

2.)SQL Server Integration Services & SQL Server Agent Services should be started before configuring Management DataWarehouse

Step 1: 

Right-click on Data Collection -> Configure Management DataWarehouse and then click Next as shown in below image

Step 2:

check the option – “create or upgrade a management data warehouse” and then click Next as shown in below image

To create management data warehouse which is nothing but SQL Server database which serves as the storage unit of collected data.

Step 3: 

click on New to create management data warehouse (mdw)database and then click Next as shown in below image

Step 4:

Map the logins & roles for management data warehouse (mdw)database and then click Next as shown in below image

  

mdw_admin role will allow users to read, write, update management data warehouse (mdw)database and execute SQL Server Agent 

mdw_reader role will allow users to read data which is available within a management data warehouse (mdw)database.

 mdw_writer role will allow users to write and upload data to a management data warehouse (mdw)database

Step 5: 

Check the configurations done so far and then click Finish as shown in below image

If creation & installation of management data warehouse (mdw)database and mapping of logins & roles are completed successfully ,then click Close as shown in below image

Step 6: 

Again right-click on Data Collection -> Configure Management DataWarehouse, check Set Up data collection  and then click Next as shown in below image

Which means data about this instance will be collected & loaded into MDW database.

Step 7:

Select a Server name& Database name that is the host for your management data warehouse (mdw) . 

Under Cache directory , if you mention path for the folder,then that folder will serve as temporary storage unit of collected data,before data is loaded into MDW database and then click Next as shown in below image.

Note: It is always considered as the best practice to keep management data warehouse (mdw)database in a separate SQL Server 2008 instance.

Complete the Data Collection Wizard by clicking Finish & Close

Step 9 :  

Try as shown below  to collect data & upload into MDW database.

Object Explorer - > Management - > Data Collection -> System Data Collection Sets - > right-click

 Disk Usage ->  Collect and Upload Now

Object Explorer - > Management - > Data Collection -> System Data Collection Sets - >right-click

 Query Statistics ->  Collect and Upload Now

Object Explorer - > Management - > Data Collection ->System Data Collection Sets - > right-click

 Server Activity ->  Collect and Upload Now

Once you start collecting data , notice the folder which you have mentioned under Cached Directory.

“.Cache”  files will be loaded into that temporary  storage unit as shown below.

Step 10: 

Try as shown below to view the reports for collected data

Object Explorer - > Management - > Data Collection ->Reports - > Management Data Warehouse -> Disk Usage Summary

Object Explorer - > Management - > Data Collection ->Reports - > Management Data Warehouse -> Query Statistics History

Object Explorer - > Management - > Data Collection ->Reports - > Management Data Warehouse -> Server Activity.

 Disk Usage Summary Report:

Query Statistics History Report:

Server Activity History Report:

You can view logs of Data Collection by right-clicking Data Collection - > View Logs

You can disable Data Collection by right-clicking Data Collection - > Disable Data Collection

What SQL Server Agent Services will do here?

SQL Server Agent Jobs will automatically collects data at regular intervals /at the intervals we mention,

SQL Server Agent Jobs  can purge and cleanup data available within a MDW database.

What SQL Server Integeration Services will do here?

SSIS will load ,collected data into temporary storage unit and then into MDW database,if you have mentioned folder under Cached Directory – Cached mode.

SSIS will load data directly into “snapshot” tables inside MDW database – if you have not mentioned folder under Cached Directory – Non cached mode.

What SQL Server Reporting Services will do here?

SSRS will generate reports about collected performance datas.

Once the  management data warehouse (mdw)database is created & installed,you will notice  set of tables,views,procedures etc created under management data warehouse (mdw)database.

--Tables,views,procedures,functions,triggers,assemblies,types,schemas installed in management data warehouse (mdw)database

USE DataCollectorDB

GO

SELECT SCHEMA_NAME(SCHEMA_ID)+'.'+OBJECT_NAME(OBJECT_ID) MDW_Tables FROM sys.tables

SELECT SCHEMA_NAME(SCHEMA_ID)+'.'+OBJECT_NAME(OBJECT_ID) MDW_Views FROM sys.views

SELECT SCHEMA_NAME(SCHEMA_ID)+'.'+OBJECT_NAME(OBJECT_ID) MDW_Procedures FROM sys.procedures

SELECT name MDW_Assemblies FROM sys.assemblies

SELECT name MDW_Triggers FROM sys.triggers

SELECT name MDW_Schemas FROM sys.schemas

--Data Collection Views

USE msdb

GO

SELECT * FROM syscollector_collection_items

SELECT * FROM syscollector_collection_sets

SELECT * FROM syscollector_execution_log_full

SELECT * FROM syscollector_config_store

SELECT * FROM syscollector_collector_types

SELECT * FROM syscollector_execution_log

SELECT * FROM syscollector_execution_stats

Data Collection option in SSMS 2008,will be really helpful for DBA ‘s in gathering performance datas from single/multiple SQL Server 2008 instance

DBA ‘s can easily find the performance problems just by viewing the report generated from data in MDW database.