In this four-part tutorial, you will learn how to use NT Server 4.0’s Performance Monitor and Microsoft Excel to monitor and analyze SQL Server performance. You will also learn how to use a SQL Server database to store your Performance Monitor logs. This tutorial assumes that you already know the basics of using Performance Monitor, Excel, and of course, SQL Server.
This is part one of the four-part tutorial. This part covers how to use Performance Monitor to capture log data. The second 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. And part four will show you how to interpret your results.
Background
Before we begin this tutorial, let’s take a moment to first discuss why we want to monitor SQL Server performance. As you probably already know, SQL Server is very good at tuning itself. It has the ability to monitor itself, and through a feedback loop, it knows how to internally adjust and tune itself so that it keeps running efficiently, even when external events, such as the number of user connections or the amount of available RAM, change over time.
But as we all know, SQL Server’s ability to self-tune is not perfect and does not take into consideration every possible aspect that affects its performance. As a DBA, we need to help SQL Server along, providing it the resources it needs for it to do a good job serving up data.
As a good DBA, we don’t want to find out from our users that SQL Server is having a performance problem. Instead, we want to be proactive and catch performance problems before they arise. That is what NT Server 4.0’s Performance Monitor can help us do. It is a tool that allows us to monitor what is going on with our SQL Server, and to provide us the information we need to make decisions on how to best tune our SQL Servers.
Performance Monitor is an important tool, because it not only provides us with information on how SQL Server is performing, but it also lets us know how NT 4.0 Server is doing, which of course directly affects SQL Server’s performance.
With this in mind, it is the goal of this tutorial is to show you how you can use Performance Monitor to capture NT and SQL Server-related data, and then analyze it using both Performance Monitor itself and Microsoft Excel using trend analysis. And because we need a place to store our Performance Monitor Logs, you will learn how to store them in a SQL Server database.
Performance Monitoring Never Stops
Monitoring the performance of NT 4.0 Server and SQL Server is not a one-time event. As a DBA, you should be constantly monitoring all of your SQL Server’s performance. This is the case whether you have one server, or a hundred.
As described later in this tutorial, you should be using Performance Monitor to capture performance counters and to store them in a log. Periodically, this log should be exported to a storage area for long term keeping. This storage area could be a folder on a network holding the raw Performance Monitor log files, a SQL Server database, an Access database, or an Excel spreadsheet, whichever best meets your needs. In our case, we will be using SQL Server to store this data.
I have chosen SQL Server for several reasons. First, it is handily available. Second, we should know how to use it. Third, it can handle all our storage needs. Fourth, it is easy to import Performance Monitor logs into SQL Server, and easy to export the log data to Excel.
Once our Performance Monitor log data is in a SQL Server database, we can then choose to export it to Excel for detailed analysis. And since the data is in SQL Server, we have the full capability of SQL Server to only SELECT and analyze the data we want to analyze in Excel.
The main reason we want to store our Performance Monitor logs is to be able to identify trends in performance. Looking at Performance Monitor logs for a day, or even a week, can’t really tell us if we will need to add CPUs, or more disk arrays, to our server next year. What we need are months of data, perhaps years of data, that we can use to make reliable predictions on what our SQL Server resource needs will be in the future.
So one of the biggest benefits of long-term NT Server 4.0 and SQL Server monitoring is to be able to predict our server needs in the future. While this important, we also need these same logs to help us identify current performance bottlenecks and other potential performance-related problems. The more information we have at our disposal, the better decisions we can make.
Selecting a Performance Monitor View
Performance Monitor has four operational “views”. They include:
-
Chart View: For performance counter viewing and analysis
-
Alert View: For creating performance-based alerts
-
Report View: For viewing data performance data in a tabular format
-
Log View: For logging performance data to a log file
Our focus here will be on the Log View, which allows us to select the Performance Monitor counters we want collect and log, along with how often we want to collect the data.
Determining Where to Run Performance Monitor
The first step to using Performance Monitor is to decide where it will be located when it is running. You can locate it on the same server that it is monitoring, or you can locate it on any NT 4.0 Server or Workstation. If you run Performance Monitor from the same server you are monitoring, this will affect the results, although not significantly on most servers.
I prefer to run Performance Monitor from NT 4.0 Workstation, connecting to the monitored server over the network. This will move most of the load off of the server being monitored, with the very minor exception of some network activity.
Another option is to use the service-based version of Performance Monitor available with the NT Server 4.0 Resource Kit. It allows you run Performance Monitor as a service, not as a foreground application. This can help reduce the overhead of running Performance Monitor. For this tutorial, we will be using the standard Performance Monitor software that comes with NT 4.0 Server and Workstation.
Once you have decided where to run Performance Monitor, the next step is to decide how many SQL Servers you want to monitor. Performance Monitor can monitor up to 10 different servers simultaneously. If you decide to monitor more than one server at a time, then you will definitely want to dedicate a computer, such as a NT 4.0 Workstation, for this task. If you need to monitor more than 10 servers at a time, you can open multiple instances of Performance Monitor on your computer.