Blog Post

T-SQL Tuesday #62 - Achieving Healthy SQL with MDW


T-SQL Tuesday #62


Tuesday, January 13, 2015 is the 62nd entry and first T-SQL Tuesday

of the year 2015, and I am honored to be this month’s host to kick of the New

Year!  Last week, I made the

announcement, and invited everyone to the blog party started many quantum ago,

by SQL MVP Adam Machanic (b|t). 

Here’s the official invite to Healthy SQL with details and T-SQL Tuesday’s

blogger’s bill of rights.

Please join

us and write about your resolution for a Healthy SQL Server environment, and

what you would do to ensure your SQL Servers are at their peak performance and

are achieving SQL Fitness.


way to do this is to perform regular health checks of your SQL Servers, just

like people, to make sure they are up to date with all its optimizations.  There are so many ways to maintain a healthy

SQL Server environment that one could write a book on it! 


am looking forward to all of your blog posts today, which must be published

between 00:00 GMT Tuesday, January 13th, 2015 and 00:00 GMT

Wednesday, January 14th, 2015. 

Don’t forget to make sure your post contains the T-SQL Tuesday logo

image on the original invite, and use trackbacks

or post a comment to this post with a URL link to your post. Finally, be a

T-SQL social butterfly and tweet about your post using the #TSQL2sday hashtag.


does one start with their health checks. 

In quick summary, start with an inventory of your SQL Servers, create a

checklist of what data you will need to collect, and then begin your collection

process by establishing a baseline.


regular health checks will enable you to get ahead of potential performance

issues before they arise. When creating and maintaining your baselines, you

will immediately be aware of any deviations from normal operating server and

database behavior.       

      If you need

a quick start way to setting up continuous performance metric collection, and

looking to create a central repository of performance metadata, with some neat

canned reports all in one, AND your SQL Servers are version 2008 and higher,

then I suggest you explore a gem of a feature called Management Data Warehouse

(MDW), which is available out-of-the-box.

So what exactly is this MDW that

is spoken of?  MSDN describes it as, “the management data

warehouse is a relational database that contains the data that is collected

from a server that is a data collection target. This data is used to generate

the reports for the System Data collection sets, and can also be used to create

custom reports. The data collector infrastructure defines the jobs and

maintenance plans that are needed to implement the retention policies defined

by the database administrator.”


Server 2008, the key data collection engine for the MDW was introduced, called

the data collector. The data

collector is a component of SQL Server 2008 that collects different sets

of data, and can be set to run continuously or at a user-defined scheduled

interval.  The data collector can be used

itself to set up custom collections, and even add them to be integrated with

the MDW.  I recommend your familiarize

yourself with the data collector – the data collector is your friend – by first

reading Introducing

the Data Collector, up on TechNet.

      MDW allows

you to very easily and rapidly deploy this system to collect, store,

analyze and report on performance stats.  MDW is

a great way to baseline the performance of our SQL Servers, trend it

over time, and use it for monitoring and reporting on the state of the health

of the SQL Servers. If you’re a DBA starting out, or an accidental DBA, or even

a SQL Pro, and want to set something up fairly fast, then you would use the MDW

Wizard to step you through the process. 

To learn more on how to Configure the

Management Data Warehouse, click on the highlighted link to go to the MSDN article

as entitled.


where is this MDW thingy?  Well, if you

have SQL Server Management Studio, then you’re all set to go.  Connect to the SQL Server you would like to

use as a central repository of performance metadata, navigate SSMS to the

<Management> folder, drill-down to the <Data Collection> item, and

then right-click.  You will see a pop-up

menu, and now select <Configure Management Data Warehouse>, as shown in the screenshot below:




you launch the wizard, you will be presented with two task choices, 1. Create

or upgrade a management data warehouse; and 2.Set up data collection.  When launching the MDW wizard for the first

time, you will select the first option to create your warehouse, and continue

to follow the prompts until your database is created.  The "Select Configuration Task" screen is shown here:

In addition, you'll

need to map logins and users to the proper MDW roles.  There are three distinct roles for MDW you

should be aware of:



- highest level of rights on the MDW database. Members of this role has both

read and writer permissions for uploading data, and reports.



- this role allows users to upload the data to the MDW database.



- lowest level of rights, and used for reports.


the central MDW database repository is created, you will need to go and launch

the wizard a second time, choosing the option to “Set up data collection”.  For each SQL Server that will participate and

upload performance data to the MDW, you will need to repeat these steps for

setting up data collection.  The wizard will

take you through pointing the collector to the MDW database that was created.


are so many more things to talk about beyond the scope of today’s T-SQL

Tuesday, regarding MDW, and Health Checks. 

Hopefully, this blog post, and the many others that will be posted

today, will give you numerous tips and ideas on how to make sure you have a

healthy SQL Server environment.  You are

all on your way to achieving Healthy SQL!


for reading, thanks in advance for posting, and thank you for the opportunity

to host this month’s T-SQL Tuesday #62! 

I will be watching and reading your great posts, and resolve to have a

T-SQL Tuesday round-up real soon!  Stay healthy!  You can follow me on twitter: @Pearlknows





You rated this post out of 5. Change rating




You rated this post out of 5. Change rating