SQL Server Reference Guide

You know you’re a DBA if you’re the only person in the room that believes it’s something other than the database server if a performance problem is found. It seems to be the lot of the DBA to always have to investigate a performance problem starting with the database.

Until SQL Server 2008, you had to use the techniques I’ve described here in the performance tuning section of the InformIT Guide to figure out where to start. One of the most difficult tasks in performance tuning is to find a historic performance problem — in other words, one that may not be occurring right now.

I explained in a couple of articles there that you need to create a baseline of how your system is running to be able to compare the system later, so you can see how the system was running at one time to find the performance problems. This is largely a manual process, and if you only capture a single picture of the data you miss all the things that have happened along the way. What is needed is a good way to continuously capture the performance data.

You can buy a third-party solution to do this. While those are still good choices, SQL Server 2008 now comes with a set of features that help you work with this process without having to buy anything else.

There are three parts to the process I’ll show you in this tutorial:

1. The Management Data Warehouse
2. The Data Collector Engine running Collection Sets
3. SQL Server 2008 Management Studio Reports

I’ll start by explaining each of these features, and then I’ll put it all together by showing you how to use these components to find and fix a performance issue in your system.

The Management Data Warehouse

The Management Data Warehouse is simply a SQL Server 2008 database that you create using a Wizard, which has some special attributes that stores the performance data.

It’s best to locate this database on a single, monitoring server. If you put the Management Data Warehouse on the same system as the one you’re monitoring, you’ll cause the numbers to go higher, since in effect you’re “monitoring your monitoring.” Also, placing the Management Data Warehouse on a separate server means that several other servers in your organization can also send their performance data to this location, so you’re able to monitor lots of systems from a single server.

And that is the key — the server where the Management Data Warehouse is located is the one that takes the requests to look at the data later. That means that the other servers aren’t constantly bothered with administrators asking “how are you doing?”

To set up the Management Data Warehouse database, open SQL Server Management Studio (SSMS) 2008, and connect to the system where you will store the database.

Now navigate down to the “Management” node in Object Explorer. Expand that, and then click the “Configure Management Data Warehouse” menu item.

Figure 1

From there, you’ll be placed in the Wizard. Just click “Next” on the welcome screen.

That brings you to the “Select a configuration task” panel. We’re after the first option, “Create or upgrade a management data warehouse” in this step, so select that and then select “Next.”

Figure 2

Next, just enter the name of the server where you want to store the Management Data Warehouse database. If this is the first time you’re setting this up, just click the “New” button after you enter the server name and give the database a name on that server. I use “MDW” myself, but you can call it anything you like.

The next screen asks you to set up the security for the Management Data Warehouse database. You want to use the roles shown in this screen, but you can also map specific users. If you use the roles, then you can add users to those roles (remember, this is on the MDW server) and let whoever you want in to the system.

Figure 3

From there you get a final panel showing all your selections. Just click “Finish” here and you’re all set. Next, you’ll move on to the Data Collector part of the system.

The Data Collector Engine running Collection Sets

The next piece of the puzzle is the Data Collector. This is a new engine in SQL Server 2008 that can watch various components of SQL Server 2008, and collect data about them.

The Data Collector is the engine, and what the Data Collector collects is called a Collection Set. This is just a definition of the individual things you want to track — called Collection Items. So that looks like this:

Figure 4

The important part here is that the database (shown on the left) is on one system, and the Data Collector (shown on the right) runs on multiple systems to feed that data up to the monitoring server.

Collection Items are anything from Windows System Monitor (or Perfmon if you’re old like me ☺ ) objects and counters, Transact-SQL statements, or event SQL Server Profiler Trace definitions (more here if that’s new to you). So that means you could, for instance, set up a Collector Set that contains memory counters from Windows, query statistics from the Dynamic Management Views in SQL Server showing memory use, and a Profiler Trace definition showing the memory used by locks in the database. All that data would then be collected and sent on to the Management Data Warehouse. You can then run queries on the Management Data Warehouse to show you the data you’ve collected.

Collection Items have schedules that you can set up. In fact, there are three schedules involved: One for the time you want to grab the counter or T-SQL query, another for when you want to upload what you’ve collected to the Management Data Warehouse, and another for when you want to clean out the Management Data Warehouse. I’ll show you this next. To create a Collection Set and its Collection Items, you can type in a lot of XML statements using a couple of Stored Procedures.

But Microsoft ships three “Standard” Collector Sets with almost every Collector Item you need right out of the box. With one Wizard you can set up the Collector Sets, get the Collector Items, and set up the schedules and destinations all in one step.

To do that, open SSMS and connect to the system you want to monitor. If you’re using a test system, you can use the same server as the one where you created the MDW database, but in production you would perform these steps on the systems you want to monitor, not on the one where the MDW is stored.

Drill down to the same Wizard you used for the Management Data Warehouse, only this time on the second panel (the one after the Welcome screen) you’ll select the second option, “Set up data collection.”

Figure 5

Once you make that selection and click “Next,” you’ll move to the panel where you select the server that houses the Management Data Warehouse database. In my case, you can see the server name here and the MDW database I created. This is where the Collection Sets I’m about to set up will go.

Figure 6

Also on this panel is a directory to store the Cached files. I haven’t mentioned that until now, but the idea is that the data will be collected at one schedule (say, every five minutes) but sent to the MDW database on a different schedule (say, every hour or so). That means the data has to be stored somewhere until it gets sent, and this is the directory where that happens.

Clicking “Next” here brings up the login dialog for that server. Make sure the account you log in with is in those roles I mentioned earlier. You’re brought to the final panel where you review your selections, and press “Finish” when you’re ready.

From there, your Collection Sets are all set up, and enabled. In the next tutorial I’ll dive in a little more on the schedules and setups you see here, but for now, you’ll be collecting data and uploading it on an interval of 5-15 minutes, depending on the Collection Item.

Go ahead and use your test system as normal for about a day or so. Make sure there is some activity on the system so that it generates some interesting data.

SQL Server 2008 Management Studio Reports

After the Data Collector has been running for a day or so, you should have some data in the Management Data Warehouse that you can report on. To see the reports, open SSMS and connect to the server where the Management Data Warehouse database lives.

Navigate to the “Databases” node in Object Explorer, and expand it so that you can see the database names. Right-click the Management Data Warehouse database name (in my case, MDW) and select “Reports,” then “Management Data Warehouse,” and finally “Management Data Warehouse Overview” from the menu that appears.

Figure 7

You’ll be placed in your report. This first report shows all of the Data Collectors that sent data to this Management Data Warehouse database.

Figure 8

There are three kinds of reports here, which correspond to the Data Collector Sets you created with the last Wizard. You can click on any of the reports that have a date under them. I’ll click on “Server Activity” on my “ERP” server.

Figure 9

At the top is a date selection tool, so that you can set the range of the data you want to see. You may have to adjust this to get data to show up on the screen.

From here, you have lots of information you can see about your server based on the historical data you collected. But it doesn’t stop there. Click on any of these graphics and you’ll drill in even further to more reports — and clicking on the graphics and hyperlinks on those servers can get you even more information. I’ll help you explore these reports in more depth in other tutorials.

A Few Caveats

There are some issues with this process, however. All this graphical information is based on how active your systems are. Less activity shows less data.

Also, this is a SQL Server 2008 feature only. The reports, MDW database, and the Data Collector don’t run against SQL Server 2005 or lower systems — although you could put the same data from those systems in the MDW and the reports would still work. I’ll show you that later as well.

By default, your MDW will empty out every two weeks, and begin to collect data again. I’ll show you how to tune that interval later, and how you can save that data off if you want. Also, you’ll collect 200-500MB of data every day using the default schedules, again depending on how busy your system is.

InformIT Articles and Sample Chapters

I’ve got another tutorial on Performance Tuning methods here.

Books and eBooks

Need more help with SQL Performance Tuning, not just the server itself? Check out SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer. (Read in Safari Books Online)

Online Resources

The official reference for this feature is here from Microsoft.