This is part three of a four-part tutorial. This part discusses how to use Microsoft Excel to create analysis charts and how to perform trend analysis using Performance Monitor data. Part four will show you how to interpret your results. Read part one. Read part two.
This article uses Microsoft Excel 97 for its examples. If you have Microsoft Excel 2000 instead, you should be able to follow along with few, if any changes.
Introduction
While the Chart Mode of the Performance Monitor is not too bad a tool to visually analyze Performance Monitor results, it has a lot of limitations. Some of these limitations include the inability to easily manipulate the data, to analyze the data using various statistical functions, or to project the data into the future to help you predict future SQL Server resource needs.
To make the job of analyzing and interpreting Performance Monitor data easier, we are going to learn how to use Microsoft Excel to perform this task. The focus of this article is on how to use Microsoft Excel to create charts and how to perform trend analysis using Performance Monitor data, not how to interpret the results. That will be covered in part four of this four-part tutorial.
Before I begin, I’ll just come out and say it, analyzing Performance Monitor data with Microsoft Excel is not the most elegant approach I have seen to analyzing data. It requires more manual work than I prefer, and it doesn’t easily provide all the analysis I would like. But given my budget, and most DBA’s budgets, you may not be able to afford a better tool. I would prefer a tool dedicated to collecting and analyzing Performance Monitor data, but until then, I’ll have to settle for Microsoft Excel.
In the following sections you will learn the basics of how to use Microsoft Excel to create charts and how to perform trend analysis using Performance Monitor data. In order to follow this article, you should have a basic understanding of how to use Microsoft Excel.
Where to Start
Before you can start analyzing Performance Monitor data using Microsoft Excel, you must first answer these important questions:
Where to get the data from?
If you have followed this series of articles, then you would know that I have previously suggested that you store your SQL Server Performance Monitor data in a SQL Server table. Storing your Performance Monitor data in SQL Server makes it convenient to store and manipulate your data. For example, you can create separate tables for each of the SQL Servers you want to monitor. And as you gather more data, you can append the data to the table, allowing you to store all of your historical data in one central location. You can also use queries to select only that data you want to export to Microsoft Excel.
Of course, you don’t need to store your data in SQL Server in order to analyze it with Microsoft Excel. You can store Performance Monitor data in several formats, including native Performance Monitor files, ASCII files, in a Microsoft Access database, or any database for that matter.
No matter where you store your Performance Monitor data, you will need to select a location and use it as your central repository. It is important that all your data be handily available, and in a format easily accessible by Microsoft Excel.
Which counters do you want to analyze?
Most likely, you have collected more counters than you want to analyze. What you will want to do is select only a small handful of counters to analyze at any one time in Microsoft Excel. This is because putting too much data on the screen in Microsoft Excel makes it difficult to see what you are doing (the screen just gets too confusing). If you need to analyze more data than can comfortably fit on the screen, then you can analyze the data in groups of related counters. The actual number of counters you should analyze at any one time depends on your screen resolution (how much you can see on your screen) and how much data you are comfortable working with.
For this article, I am going to assume you know what counters you want to analyze, so I won’t mention specific ones at this time, although later you will see some examples I commonly use. But in part four of my series on Performance Monitor, I will discuss specific counters and what to look for.
What time period do you want to analyze?
Generally, there are three different time periods you will want to analyze: daily, monthly, and quarterly. Of course you can choose any time periods you want, but I find these three time frames useful for different reasons.
Daily: A daily look lets me see what is happening on an per-hour basis, looking for daily patterns, peak times, and lull times. I am also looking for counters that indicate bottlenecks. When I am performance tuning for specific bottlenecks, I use daily data the most. I also use daily data to give me a look at how well balanced my hardware is, such as how well CPUs and physical disk arrays are being equally used. In some cases, I will look at even at a range of a couple of hours if I am trying to diagnose a specific performance problem.
Monthly: On a monthly basis, I am also looking for patterns, peak times, and lull times. Often, I can use the data to help me schedule database maintenance, such as indexing a database or running large DTS imports or exports. I don’t usually use monthly data for bottleneck troubleshooting because the data is not granular enough.
Quarterly: I use long term data for trend analysis, to help me “predict” future needs. For example, I want to predict how many users will be using my databases, how much physical disk space will I need, how much I/O capacity I will need, how much network bandwidth I will need, and so on. The more data you have here, the better your “predictions” will be.
What time sampling do you want to use?
As you probably know, when you use Performance Monitor to collect counter data, you can select how often data is collected. You will want to collect it often enough in order to get enough detail for daily-type analysis, but you don’t want to have so much data that quarterly trend analysis gets bogged down.
To get around this problem, you will want to collect data at a time interval detailed enough for daily analysis, but when you want to do monthly or quarterly analysis, you will want to aggregate it so that there is not too much data. And this is where storing your data on SQL Server comes in handy.
For example, say you collect counter data every minute, and that you store this data in a SQL Server table. If you want to analyze daily data, you can select the time period you want to analyze and export it from SQL Server as is. But if you want to analyze data on a quarterly basis, you can use Transact-SQL to aggregate the data into hourly averages, and then export these to SQL Server. If you don’t want to aggregate your data using SQL Server, you can do so using a Microsoft Excel pivot table, as we will learn later in this article. You may have to experiment with different levels of granularity until you find the ones best for the types of analysis you want to perform.
What scale do you want to use?
Another issue you must address is what scale does each of the counters you want to analyze use. As you may know, some counters use a percent range, such as from 0% through 100%. Others use a quantity measurement, which can range from 0 through 10, or from 1 through 1,000,000. Scale is important because it is hard to analyze data that has significantly different scales at the same time. Generally, you will only want to analyze groups of data that have similar scales.
If you need to analyze data that has different scales, one option is to use either SQL Server or Microsoft Excel to rescale the data so that all of the data fits the same scale. You may remember that the Performance Monitor Graph Mode does this automatically. If you do choose to rescale data, be careful to remember this, because once you begin analyzing data, it is easy to forget that you have rescaled the data, and you may misinterpret the resulting charts.
Don’t discount the importance of finding the best answers to these very basic questions before you begin analyzing your Performance Monitor data in Microsoft Excel, as they will greatly affect the success of your analysis. Once you answer all of the above questions, you are now ready to import your data into Microsoft Excel.
How to Import SQL Server Data Into Microsoft Excel
As I mentioned earlier, there are many different ways to store your Performance Monitor data. For this article, I am going to assume that it is stored using SQL Server. If you are not storing your data in SQL Server, then you will have to export your data in a format that can be easily imported by Microsoft Excel.
The easiest way to export Performance Monitor data from SQL Server to Microsoft Excel is to use the DTS Export Wizard, although this is not the only option. The DTS Export Wizard is handy because it steps you the process of exporting your data from SQL Server directly into a Microsoft Excel spreadsheet format. For the most part, you just need to following the screens to find out what to do. But if you are not familiar with this wizard, here are the basic steps:
-
Using Enterprise Manager, right-click on the database that contains the data you want to export, the left-click on “All Tasks”, and then left-click on “Export Data”. This brings up the DTS Export Wizard.
-
Click “Next” on the DTS Export Wizard introductory screen.
-
In the “Choose a Data Source” screen, the “Source”, “Server” name and “Database” name should already be correctly selected. If not, then select the correct options. Click “Next” to continue.
-
In the “Choose a Destination” screen, The “Destination” option needs to be changed to “Microsoft Excel 8.0” (works for Excel 97 and 2000). In the “File Name” option, enter a path and file name for the Microsoft Excel file that will be exported. Click “Next” to continue.
-
In the “Specify Table Copy or Query” screen, you must make a decision on what data you want to export. If you want to export all of your data (which would be unlikely in most cases) you would select the “Copy table(s) from the source database” option, which lets you select one or more entire tables to export to Microsoft Excel. Instead, you will probably want to select the “Use a query to specify the data to transfer” option, as this one allows you to selectively choose what data you want to export from your SQL Server table to your Microsoft Excel spreadsheet. Once you have made your choice, click “Next” to continue.
-
Assuming you selected the “Use a query to specify the data to transfer” option in the previous step, the “Type SQL Statement” screen is displayed next, offering you two ways to enter a query in order to specify which data you want to export. The easiest way is to use the “Query Builder”, which allows you to point-and-click to create a simple query to select your data you want to export to Microsoft Excel. But if your query is complicated, such as you want to aggregate the data before you export it, you will have to enter the SELECT statement manually in the “Query Statement” window. If you do this, I would recommend you write your SELECT statement using the Query Analyzer first, as using Query Analyzer makes writing and debugging the query much easier. Once the query is debugged, you can cut and paste it into the “Query Statement” window. Once you have entered a query (however you created it), click on “Next” to continue.
-
In the “Select Source Tables” screen, you have the option to perform column mapping and transformations on the data. In most cases you will probably not need to do this, but it is available for advanced users. Let’s assume you don’t need this option, so click on “Next” to continue.
-
You have now completed the DTS Export Wizard. At this point you can run the export immediately, or you can save it, or you can do both at the same time. If you plan on performing this same task over and over, you may want to save it as a DTS package. That way, you can edit the DTS package if you need to make any changes before the next time you use it. Let’s assume for now that we want to save this DTS package as a SQL Server object, so select this option and click on “Next”.
-
In the “Save DTS Package” screen, enter a name for this DTS package and click on “Next”.
-
The “Completing the DTS Wizard” screen appears. To run the DTS package, click on “Finish”.
The DTS package should now run, and after several seconds, display a message telling you that it was successful. Click on the “OK” button to continue.
Now that the data you selected from SQL Server has been exported to an Excel spreadsheet, you are ready to start analyzing the data graphically.