This is part two of a four-part tutorial. This part discusses how to use SQL Server to store Performance Monitor log data. Part three will show you how to use Microsoft Excel to analyze the Performance Monitor Data. Part four will show you how to interpret your results. Read Part One. Read Part Three.
Review
In the first part of this series, we took a look at how you can use NT Server 4.0’s Performance Monitor to collect performance data from your SQL Servers. While the Performance Monitor tool is great for collecting performance data, and fair at analyzing it, it is very poor when it comes to storing the performance data for later use.
Performance Monitor stores the log data it collects in a proprietary format that cannot be easily used by other software. But what you can do is to export the data from the Performance Monitor into the ASCII format so that it can be imported into other software, such as SQL Server, for storage.
Why Do We Need to Store Performance Monitor Data?The whole purpose of this article is to learn how to do SQL Server performance trend analysis. When we talk about trend analysis, we are talking about collecting SQL Server performance data every day, day-after-day, storing it, and then analyzing what is happening over various time periods.
For example, as a DBA, it is important for me to know how many users are using my server. In addition, I need to know: Is the number going up or down? Is the number changing slow or fast? Are the users light or heavy users? If the number of users is going up fast, will this significantly impact SQL Server’s performance? Unless you have hard data to review, it is hard to make wise decisions about what to do to ensure good responsiveness of your SQL Server for your users.
We want to collect data over long periods of time in order to provide the data we need to help identify current performance issues and to help plan for the future. Is the current surge in CPU activity due to more users, or because the same number of users are running more queries? To answer this question, and hundreds of more questions like this one, you must have good historical performance data. Without it, you can only guess as to what’s currently going on.
Predicting the future, at least as far has hardware needs goes, is also important. Most DBAs just can’t go to their boss and request that a new server be installed tomorrow to resolve a newly discovered performance issue. Instead, the DBA needs to try to project future needs, and before the need actually exists, begin the often long process of initiating the purchase of new equipment. This process often requires the DBA to justify the request, and if good data exists, then the justification process is made much easier.
If you are a DBA of one SQL Server, or hundreds of SQL Servers, you need to collect Performance Monitor data, and then store it for easy retrieval later.
Microsoft Doesn’t Make It Easy to Store and Analyze Performance Monitor Data
When Microsoft designed Performance Monitor, they apparently didn’t plan on people using it to store data for easy later retrieval. While this article will show you how store Performance Monitor data in SQL Server, this task is not as straight-forward as it should be. In fact, once you read this article, you might decide not to bother because of the hassle.
If you do come to this conclusion, then you are making a short-sighted mistake. Yes, it is somewhat of a hassle, but overall, it is worth it.
But what if you don’t have the time needed to collect and store your Performance Monitor data? If you don’t have the time, but if you have the money, you may want to consider purchasing third party tools to make the task of collecting, storing, and analyzing Performance Monitor data.
While there are a number of tools on the market that collect Performance Monitor data, there are two that I am specifically familiar with, and they include Event Log Monitor from TNT Software, and AppManager from NetIQ. Both tools will automatically capture SQL Server performance data and store it in a SQL Server 7 database.
But if don’t have the money for specialized software, then you need to store your Performance Monitor data manually, as described in this article. As you read the steps I have outlined below, keep in mind that you don’t have to do every step exactly as I describe. The key thing to keep in mind is the big picture, the details of how you move data from the Performance Monitor to SQL Server is not critical. What is critical is that you save your performance data for later trend analysis.
Overview of How to Store Performance Monitor Data Using SQL Server 7.0
Below are the steps you can follow to export SQL Server Performance Monitor data from Performance Monitor to SQL Server 7. Moving data to SQL Server 2000 should be very similar, although a few of the minor details will be different. These steps assume you have already collected SQL Server Performance log data using the techniques described in part one of this article.
In brief, here is what you need to export data from Performance Monitor and to import it into SQL Server:
-
Point to the log data using Performance Monitor
-
Select the counters to export from your log data
-
Export the data into the comma-delimited ASCII format
-
Clean-up the Performance Monitor data using Microsoft Excel
-
One time only, create a database to store your performance monitor data
-
Use DTS to create a package to import the Excel data into a SQL Server table
-
Import the Excel spreadsheet data into a SQL Server table
Point to the Log Data Using Performance Monitor
-
Before you can export the performance data from Performance Monitor, you must first point the the log that contains the data you want to store in SQL Server.
-
To do this, start Performance Monitor and select the Graph Mode if it is not already selected. Next, select “Options” from the drop-down menu and then select “Data From”. This displays the “Data From” dialog box.
-
In the “Data From” dialog box, click on the “Log File” radio button, then click on the browse button (the button with three periods). This displays the “Open Input Log File” box. Here find and select the log file you created earlier, then click on the “Open” button. You are returned to the “Data From” dialog box. Now click on the “OK” button. Now, Performance Monitor knows where the log file is and can work with it.
-
Your next step is to select the counters that you want to store in SQL Server for later trend analysis. As was discussed in part one of this four part series, data for every counter is collected for every object you selected when creating your log file, even if you didn’t want to monitor or analyze all the counters for all the objects you created. This means your next step is to select only those counters we want to store in SQL Server, while ignoring all the rest. While you can attempt to store all the counters for all the objects in SQL Server, you will probably end up with too much data, most of which will probably never be used.
-
The way you select only the counters you want to store in SQL Server is to add the counters to the Graph Mode of the Performance Monitor, just like you do when you monitor counters in real-time in the Graph Mode. You will need to add every counter you want to track from every object that was logged.
Export the Data into the Comma-Delimited ASCII Format
-
Once you have added all the counters to the Graph Mode of the Performance Monitor, the screen may be very hard to read because there is just too much data to easily see. Don’t worry about this as it does not affect how we export the counter data. Your next step is to save all these counters so that you won’t have to reselect them all over again the next time you need to import them.
-
You can save these counters by clicking on the “File” menu option and then selecting “Save Chart Settings”. You will be presented with a dialog box where you can save a file, with a name you assign, that contains all your settings. These settings then can be retrieved at any time later by clicking on the “File” menu option and then selecting “Open”. This displays another dialog box you can use to select and load the file that contains all of your counters.
-
Now that you have selected all the counters you want to export and store in SQL Server, you are ready to perform the actual export from Performance Monitor. To do this, click on the “File” menu option, and then select “Export Chart”. This will display the “Performance Monitor – Export As” dialog box. Here, you decide where you want to store the export file, the name you want to give to the export file, and you choose which format to store the format in. The default export file format is “Export TSV Files”, which is the Tab-delimited ASCII format. Instead of using the default format, select “Export CSV Files”, which exports the data in the Comma-delimited ASCII format. Either one will work, but the example I use in this article uses the Comma-delimited format.