• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

SQL Server Management Data Warehouse (MDW) – Why it Rocks

April 16, 2013 by John Sansom 17 Comments

Ensuring excellent performance for users and customers is a high priority for Database Administrators.

Today I want to share a free and easy way for you to get valuable insight into the performance of your Microsoft SQL Server instances.

There are so many features in the SQL Server product that it’s easy to miss some really good stuff. The Management Data Warehouse (MDW) is one of the most underutilised features that has the potential to provide significant value for DBAs.

The Management Data Warehouse (MDW) In a Nutshell

The MDW is a suite of SQL Server technology components that together provide the DBA with a tool for streamlined performance troubleshooting.

The main components are:

  • The Data Collector – A system for automatically collecting performance data about SQL Server.
  • The MDW Database – The database where the performance data is stored.
  • The MDW Reports – Ready made interactive reports for easily reviewing the data.

The streamlined nature of the solution means that once you have defined your configuration its very straightforward to extend it to monitor the performance of multiple SQL Server instances. This is a particularly attractive benefit for those looking to easily and consistently monitor performance across entire SQL Server environments.

To give you an idea of the potential here, I have customers that are using the MDW to monitor production environments containing hundreds of SQL Server instances successfully.

Knowledge is Power

MDW Dashboard Report

Every Outstanding DBA knows that knowledge is power and that it is important to keep your house in good order.

Using a basic configuration of the MDW enables a DBA to perform tasks such as:

  • Proactive Tuning
  • Historical Query Analysis
  • Performance Baselining
  • Database Growth Forecasting
  • Storage Planning

With just a few simple mouse clicks you can answer questions such as:

  • What are my most expensive queries in terms of a given resource measure (CPU/Duration/IO )?
  • What is my SQL Server instance Waiting on typically?
  • Which are my fastest growing databases?
  • Why is query X blocking?
  • Is SQL Server busier than normal for this time of day?

The interactive nature of the reports mean that you can easily drill down into specific areas of interest.

For example, if a high proportion of an instances WAITS are allocated to the CPU group, then simply clicking the bar on the chart will take you through to the queries that are responsible for that workload. From here you can review various execution statistics for your troublesome queries, as well as the associated execution plan for further investigation.

Building a “Next Level” MDW

Once you’ve got the performance tuning bug and have seen just how much value can be achieved through understanding the data collected via the MDW, you’ll start to wonder how you can take things even further.

Developers are super smart folks (no really) and when you take the time to establish good relationships they can often become some of your biggest advocates. I’ve seen great success in this area with customers, where rather than the database team hoarding all the insight, they actively take steps to make it available to others to benefit and learn from.

One of the ways in which I particularly enjoy using the MDW with my customers is by using it to provide SQL Server production performance insight and data to developers.

By building upon a solution shared by Bill Ramos on his blog, we have been able to use SQL Server Reporting Services(SSRS) to extend the standard MDW solution to make the data easily available to others. For example, using SSRS means that developers can set up a Subscription, say weekly, to a report that provides “the top 10 worst performing queries” for a server of interest to them.

This collaboration and knowledge sharing of SQL Server performance insight provides a great platform for proactive tuning and improvement within the organisation.

Start Benefiting from the MDW Today

Good news chaps! This brilliant SQL Server feature is not limited to just those lucky folks running Enterprise edition, it’s available in all Editions (excluding the Express flavours). As a Database Administrator, if you are not already using the MDW in your environments then you could be missing some great opportunities.

For a walk-through of how to install the MDW in your environment consult the Microsoft reference: SQL Server 2008 Management Data Warehouse – written by Kalen Delaney.

The Management Data Warehouse is just one of the many methods available that you can use to gather performance insight and opportunity for improvement of your SQL Server environments.

For more great ideas to make SQL Server administration easier check out my Top 10 Free SQL Server Tools post.

Filed Under: Administration, SQLServerCentral Syndication, SQLServerPedia Syndication Tagged With: Management Data Warehouse

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy