The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Yesterday we talked about SQL Utility. Today let’s take a look at it’s predecessor but no less relevant feature MDW and specifically how to work with it inside of SSMS.
Management Data Warehouse
Introduced prior to SQL Utility, Management Data Warehouse is a similar suite of tools Microsoft simply called MDW. There is some overlap, but while SQL Utility focuses more on health and utilization from a server and hardware perspective at an enterprise level through a specific set of metrics, MDW allows you to collect more query and performance oriented metrics and depth at an instance level as well as offering more in the way of customized Data Collection Sets.
Setting up MDW to store these data collections is pretty simple. Open up the Management Node in the Object Explorer, right click on Data Collection, and select ‘Configure Management Data Warehouse’ from the Tasks item.
Data Collection Sets
With an MDW database configured you can now configure and enable the built in data collections as well as add additional collection sets. Management of these collection sets is accomplished through the wizards and UIs available in SSMS.
A typical usage scenario is to set up MDW on a central collection server and then enable Data Collection on a target server pointing to the MDW for storage and analysis. The initial configuration wizard allows you to specify the location of your central MDW database and set other configuration choices. While SSMS won’t stop you from doing it, you should always set up MDW on a separate server than one of the ones you are monitoring. That way the performance impact of MDW doesn’t adversely affect the performance monitoring you are attempting to achieve. Once set up, the collections run on schedules within the SQL Agent of the target server.
When finished you will now have the default collection sets enabled on your target server as shown here. Note that a couple of these are new to SQL 2014. Double clicking any of these collections brings up a UI that allows you to configure frequency of collection, retention settings etc. If you look closely at the ‘Input Parameters’ window you’ll notice the collection data is based on a T-SQL statement that does that actual data collecting. If you script out the configuration instead of using the GUI you’ll get a good idea of what configuring your own collection sets entails.
The GUIs that are available in SSMS get you 90% of what most folks do with MDW. If you really want to get fancy however you’re going to have to go deep into the stored procedures and architecture around MDW. That is way beyond the scope of this series on SSMS but I’ll point you to books online if you want to get started on custom sets, though I recommend getting knowledgeable on the default architecture first.
Once you have a central MDW storing the data being collected by Collection Sets you access the information most easily through the standard reports that come with MDW. Right click the MDW database and look under Reports for the Management Data Warehouse Overview report. This is the jump off point for basically all the other reports. As you start adding custom collections you can add custom reports as well.
I mentioned however that there are new collections and associated reports unique to 2014. This is the Transaction Performance Analysis Overview report. It and it’s associated collection sets are to assist you in moving tables and procedures to in memory features offered by 2014.
Monitoring a full enterprise collection of database servers is another area where 3rd party applications such as Solar Winds (formerly Confio’s) SQL Ignite, SQLSentry, Idera and Redgate tools are generally considered superior. That is just an opinion of course. Many organizations do quite well with the built in offerings within SSMS and SQL Server. The cost of 3rd party applications can be prohibitive but frankly, if you can afford Enterprise Edition the price of some of these other tools may only be a minor additional expense. With all that said don’t overlook what’s available right through SSMS for free when shopping around if your needs are basic or you are capable and willing to configure sets that meet your specific needs.
Tomorrow we’ll do one more day on monitoring with SSMS and see the new GUI interfaces that Microsoft offers for working with Extended Events.