Disk usage, Query Statistics and Server Activity are most important information for helping administrators to identify SQL Server performance problems. SQL Server 2008 has a feature of Data Collection that can gather information about Disk usage, Query Statistics and Server Activity and take a chance for view these result by set of specific reports.
Actually, Data Collection creates integration services package that is used to gather data and then put it to specific database (database whcih created by data collection configuration.) this database sometimes called Managment Data Warehouse (MDW).
Data Collection also create a set of jobs for gathering required information and add new tables also in MSDB database to save logging and other configuration information.
Benefits of Data Collection
Central Data Repository : One database store all information related with performance.
Selected data that needed to collect : It can select specific collection set to collect and upload to database warehouse.
Display Performance Reports : View specific report for speciafic field of performance (Disk usage, Query Statistics and Server Activity)
Configuration Data Collection
Before starting configuration of data collection, Sart SQL Server Agent service. then goto Managment folder in SQL Server instance and then right click on Data collection icon .
from the list of Data Collection select Configure Managment Data Warehouse.
Click the Next button
before working with Data Collection, it must be configured first by choosing the first option.
This form need the database that will store Data Collection information (MDW), select one of listed database or create new one.
Click New Button to create new database (if needed)
What is the logins that need to maped in MDW database that is specified in prevous step . select logins from the first grid and map it to suitable database role membership in the second grid.
Now the configuration of Data Collection Successfully finished .
Set Up Data Collection
It is now the time of set up Data Collection to enable it work . To do that return again to Data Collection icon in the Managment folder in you instance server. Right click on it select Configure Managment Data Warehouse .
in the Select Configuration Task form select the second option to set up Data collection .
Specify the previous database created for Data Collection (MDW database). the collection data by default cached to Temp directory before upload it to MDW database. if needed to change temp directory, type the alternative path in Cache direcroty field that shown below.
after review all steps click Finish to start set up Data Collection.
Now Data Collection ready to Collect and upload result to created MDW database that let to administrator to review result by reports of Data Collection .
The results of set up data collection .
the set up od Data Collection create three Collection Sets named (Disk usage, Query Statistics and Server Activity) each of them collect and upload specific information in MDW database .
Note that all collection sets start by default, it can be stoped by right click on specific collection set and then select Stop data Collection Set option.
It can specify also certain data collection set to do it's collection and upload job by option of Collect and Upload Now .
Also note the creation of the following jobs in SQL Server Agent. Each of collection set has specific jobs for collect and upload data.
Reports of Data Collection
Right Click on Data Collection, Select Report > Managment Data Warehouse. then the list of report will show three kinds of report (Disk usage, Query Statistics, Server Activity)
Disk usage
Query Statistics
Server Activity
No comments:
Post a Comment