The Management Data Warehouse is used as a data store to house the performance data collected on a scheduled basis, that allows you to analyse historically what has been happening in your environment. This can be used to troubleshoot performance issues, or to identify when a change occurred that resulted in a performance issue. Alternatively this can also assist in capacity planning with your environments. To be able to have this information we need to configure a Management Data Warehouse and setup the scheduled collection of appropriate data.
Before we start our configuration and creation of the Management Data Warehouse (MDW) we need to think about where we are going to house this. What is this MDW doing? It is going and collecting data based on collection sets from possibly multiple instances of SQL Server in your environment. Due to this we do not want to have this MDW created and residing on our primary database instance. If you have the ability to provision a VM specifically for housing your MDW this will mitigate this potential performance degradation on your primary instance.
Configuring Management Data Warehouse
Expand Management, right click Data Collection, select Configure Management Data Ware House, Click Next.
We can see there are 2 options to choose from. As we are just starting out we need to create a database that we are going to use to store the data that we capture in. So we are going to select Create or upgrade a management data warehouse and click next.
Confirm the ServerInstance to configure the Management Data Ware House on. Create a New Database which will be used to store the collected data, Click Next.
If you have an AD Group that your DBA’s belong to then add that group at this stage. For this walk through I am staying with the defaults. Click Next.
You are now ready to complete the configuration steps. Click Finish.
Click Close.
Configuring Data Collection
Now that we have created our Management Data Warehouse on our newly provisioned VM we are ready to configure the data capture from our remaining SQL Server Instances in our production environment. To achieve this we need to configure each of our SQL Server Instances to utilise the newly configured Management Data Warehouse.
Expand Management, right click Data Collection, select Configure Management Data Ware House, Click Next.
We can see there are 2 options to choose from. As we are just starting out we need to create a database that we are going to use to store the data that we capture in. So we are going to select Set up Data Collection and Click Next.
Select the ServerInstance that you previously configure for the your Management Data Warehouse, Choose your Database. With setting the cache directory, from a performance view point if you are able to provision a disk specifically for this function so as to minimize the I/O impact on your data, log and tempdb drives. Click Next.
Click Finish to complete your data collection setup.
Click Close.
The completion of the configuring data collection on each of the instances of SQL Server will have created some SQL Agent jobs that collect and upload the data from each instance into the centralised Management Data Warehouse.
Modifying the Collection and Upload Schedule
Some things to consider are if you have multiple instances in your environment that you are going to incorporate into this collection, then when identifying the upload schedule you will want to stagger the schedules from the various instances.
The number of days to retain the data in the Management Data Warehouse defaults to 730 days. You need to take into account the number of databases from each of the instances that you are collecting data from, the frequency with which you are collecting the performance data to determine the number of rows of data that is going to be collected for each of the types of data collection you are going to configure. To calculate this you can use the following:
Times per day * Number of Databases * Retain Duration
Eg : 4 * 50 * 730 = 146,000 rows.
The Disk Usage Collection Set has 2 sets of Counters. The Query Statistics Collection Set has 1 set of Counters, The Server Activity Collection Set has 2 sets of Counters. The calculation above is for a single performance counter being collected. We have identified that there are 5 collection sets and they contain many counters being collected. You need to manage this very closely as your Management Data Warehouse can grow very quickly consuming large amounts of disk space.
Data Collection Sets
There are 3 Data Collection Sets created when you configure an instance of SQL Server to capture data to send back to your Management Data Warehouse.
Disk Usage
Like its name indicates this collects data on the data files and T-log files for each of the databases in the instances of SQL Server that have been included into the data collection.
Query Statistics
This collection set contains query execution statistics for the instances of SQL Server included in the data collection. This can give you the Top Queries by (CPU, Duration, Total I/O, Physical Reads, Logical Writes). To dive deeper you are able to select a detailed sub report which allows you to view the execution plan for the selected query.
Server Activity
This collection collects information around the resource usage by the Server and the Instance of SQL Server. The types of resources that you can see are:
-
CPU
-
Memory Usage
-
Disk I/O
-
Network Usage
-
SQL Server Waits
-
SQL Server Activity
Analyzing Data Collection Sets
On each of the instances that you have configured the collection sets, there are some pre-configured reports available which you can use to analyze each environment. On 1 of your configured environments, expand Management, right click Data Collection, Reports, Management Data Ware House and you will have 3 reports that you can choose from.
The 3 base reports give you the ability to drill through and get a finer grained view of what is happening, so explore on the relevant reports to get a good feel of what your system is doing.
Enjoy using Management Data Warehouse in SQL Server 2012.
Leave a Reply