How to Graph Performance Monitor Data in Microsoft Excel
While there are many different ways to analyze Performance Monitor data, there is one key approach, and you should already be familiar with it. This approach is to analyze the relationship between one or more Performance Monitor counters with time as the dependant factor.
This is where you plot time on the x-axis (horizontal axis) and the counter data on the y-axis (vertical axis). This method of analysis is important because our overriding goal here is to improve performance. And boosting performance is just another way of saying you want to accomplish more in the same amount of time. This is why time is our dependent factor. When we analyze Performance Monitor counters, what we want to see is how they vary over time.
The easiest way to analyze Performance Monitor counters over time is to visually analyze the data using graphs, much like how you using the Graph Mode of Performance Monitor. The advantage of Excel over Performance Monitor’s Graph Mode is that it is much more flexible, helping you to better visualize and analyze your data.
Once the Performance Monitor counters (for the time range you are analyzing) have been imported into Microsoft Excel from SQL Server, you are ready to begin graphing and analyzing your data.
While there are many different ways to graph data in Microsoft Excel, we will use the Microsoft Excel Chart Wizard to perform our analysis.
Let’s assume our Microsoft Excel spreadsheet looks like the one below. Note that I am graphing 15 second intervals over a period of a couple of hours. We will look at longer term analysis in the next section.
Assuming that the spreadsheet’s rows represent time intervals and the spreadsheet’s columns represent specific Performance Monitor counters, we can follow these steps to create Performance Monitor counter analysis charts using the Microsoft Excel Chart Wizard.
-
The first step, before actually starting the Microsoft Excel Chart Wizard, is to select all the data you want to graph at this time. In my case, I am selecting all the cells starting from cell A1 to cell E200. You can select as few, or as much data as you want when creating your graphs. It is important that the column names be directly above the data as seen in the above figure.
-
Now that you have selected all of the data you want to analyze, the next step is to start the Microsoft Excel Chart Wizard by selecting Insert|Chart from the drop-down menu.
-
Step 1 of the Microsoft Chart Wizard asks you to select the “Chart Type” you want to create. You have many choices, but the most common chart for analyzing time-based data is to select a line “Chart Type”. Once you select a “Chart Type”, you next need to select a “Chart sub-type”. You can choose any sub-type you find most useful, but generally, I pick the simplest one, in this case, the “Line”, as it is easier to interpret. Once you have made your “Chart Type” and “Chart sub-type” choices, click on “Next” to continue.
-
Step 2 of the Microsoft Chart Wizard allows you to change the “Data range” (the cells being graphed) and the “Series in” (tells Excel if the Performance Monitor data is in columns or rows). Assuming your data looks like the above illustration, and you selected all the cell containing the data before you started the Chart Wizard, then no changes have to be made to this screen. Click on “Next” to continue.
-
Step 3 of the Microsoft Chart Wizard allows you to “dress up” the chart. Note that at the top of this screen are a variety of tabs. Feel free to experiment with these, but the only “dressing up” I only recommend is to add a “Chart title” on the “Title” tab. If you make too many changes, it makes the graph harder to read. Click on “Next” to continue.
-
Step 4 of the Microsoft Chart Wizard specifies where you want the graph to be created. I find it easier to create the graph in “As a new sheet”, and I assign it a new sheet name. If you select the default “As object in”, your chart will be created on the same pages as the spreadsheet data you are graphing. Click on “Finish”.
The new created chart will now appear on your screen. See mine below.
If you are new to Microsoft Excel charting, you may not like what you see. Generally, the biggest mistake novices make is to put too much data on the chart, which makes it hard to analyze. If you don’t like your chart, at least it is easy to start over. Also, you can modify your chart using Excel’s many chart editing tools. One of the most common things you might want to change about a chart’s appearance, after creating it, is the X- and Y-axis’s scale or labeling. This is because the default scale and labeling created by the Microsoft Excel Chart Wizard isn’t always the best.
Since this tutorial is not how to use Microsoft Excel’s graphing feature, we are done looking at how to graph Performance Monitor data. If you want to learn more, use Microsoft Excel’s online help to learn how you can “dress-up” your charts for presentations to your boss.
Now that we have our chart, how do we interpret it? As I have mentioned earlier, we will be saving this to part four of this four part tutorial on Performance Monitor. But until them, the most obvious place to start is to look at the relationship among the various counters and time. What you are looking for are trends, patterns, or anything that looks out of the ordinary.
How to Do Trend Analysis on Performance Monitor Data
In the previous section we looked at analyzing historical data. In this section, what we are going to be doing is to take this same historical data and project it into the future.
Why? As a DBA, it is our responsibility to plan for the future, letting our bosses know that we are running out of disk space, or that we will soon need to add an extra CPU to the server. While we often don’t need fancy charts and analysis to know the answers to these questions, we often have to make a case for our recommendations. And one of the most effective way to present your case is to project current trends into the immediate future.
This can provide the best “hard facts” you have available to make your case. If you can show on paper that you have made a substantial effort to analyze the data, and that the data proves your case, then it will be much easier to get the added hardware you need to keep your server’s running at optimum performance.
While the Performance Monitor’s Chart mode provides very similar results as Microsoft Excel (as we saw earlier in this article), what Performance Monitor cannot do is project data into the future. This is where the real power of Microsoft Excel comes into play.
How Can Microsoft Excel Project the Future?
Microsoft Excel provides a number of statistical techniques to take historical data and project it into the future. Each of the techniques provided by Microsoft Excel for projecting the future has their own pros and cons. Since this is not an article on trend analysis and forecasting, we won’t discuss all the possible options. What I am going to do is show you how how to perform a very common and simple analysis called linear regression. You may remember if from you school math classes. And don’t worry, you don’t have to remember any math, Microsoft Excel will do most of the work for you. Of course, if you like math, then you may want to get fancy and check out some of Microsoft’s Excel’s other statistical techniques.
Before You Begin
Microsoft Excel offers two main ways to perform trend analysis, either through the use of statistical functions (such as TREND), or by using charts. Now you know why I introduced you to Microsoft Excel Charting in the previous section. We are going to keep our trend analysis as simple as possible, and we are going to create trendlines using the charts I showed you how to create earlier.
The first step to performing trend analysis with Microsoft Excel is to create a chart based on the data you want to project, similar to how we described above. But when it comes to projecting the future based on historical data, the more historical data we have, the more accurate our projections will be. While is is possible to only use a week’s worth of data and use it to project the future, the results wouldn’t be very accurate. Generally, I prefer to have at least three months of historical data before I perform any trend analysis. The more historical data I have, the better the projections will be.
Another issue, which has already been briefly discussed, is how granular should your data be? At the very most, I would want the data being projected to be based on hourly averages. The more granular the data, the more information Microsoft Excel has to better project the future. But the more granular the data, the more data you have to work with, which can be a hassle in Microsoft Excel as it has a finite number of rows and columns.
Also keep in mind that the historical data you will be analyzing must represent the typical data of your day-to-day production of your SQL Servers. If the data you have includes non-standard data (such as data that came from a one-time test), you don’t want to include that data along with the rest of your data. Also, the historical data must be contiguous, it must not include any gaps, such as a missing a day here and there. It is important to gather performance monitor data on a regular basis and store it in one central location, otherwise, performing trend analysis is difficult, if not impossible.
Also keep in mind that you can’t project into the future too much, as there are just too many unknowns. Generally, I prefer not to perform trend analysis for a time period greater than one half of the time covered by my historical data. For example, If I have three months of historical data, I don’t generally try to project more than six weeks into the future. If I have six months of historical data, then I can project about three months ahead, and so on.
Now if you have a good background in statistics, you may cringe over my recommendations. But my goal here is not be be “statistically correct”, but to provide a relatively simple way to identify trends and to see what might happen in the immediate future assuming the trends were not to change. If you want to be “statistically correct”, you always have that option. But if you are like most DBA, statistics is not your specialty, so I am keeping things simple here.