SQL Server 2012 : Management Data Warehouse

Views 2784

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_2

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_4

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_6

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_8

You are now ready to complete the configuration steps. Click Finish.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_10

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_12

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_14

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_16

Click Finish to complete your data collection setup.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_18

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.

Windows-Live-Writer-SQL-Server-2012--Management-Data-Warehou_8E0F-image_20

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

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search