How can I capture performance data over time, and review it later?

Question

What is the best way to capture Performance Monitor data and review it over time? I want to be able to produce baseline performance data for my SQL Server so I can compare past performance with current performance.

Answer

There are a variety of options you can consider. Each has their pros and cons.

One option would be to use Performance Monitor (System Monitor) to capture relevant Performance Monitor data and then save it in the form of Performance Monitor logs. This way, you will be able to retrieve any log you want and redisplay it from within Performance Monitor. If you want to compare the past with now, you can open two instances of Performance Monitor at the same time, each displaying a different time period, and then visually compare them. To make this work well, you would want to have logs that span the same period of time, such as a day, or a week, in order to make comparisons easier. Also, you would want to come up with a naming scheme that made it easy to identify the logs when you want to compare them.

This option is cheap and doesn't require a lot of work. On the other hand, it is not very flexible and you can't produce nifty reports to impress your boss, or easily make predictions of future trends.

Another option is to take the same logs collected above, but then export them to ASCII, and them import them into a SQL Server database. Then when you are ready to analyze them, export them to an Excel spreadsheet.

As you can imagine, this is a lot more work, but it is much more flexible and allows you to produce projections (trend analysis in the Excel Spreadsheet) and you can produce much nicer graphics.

Another option is to use a third-party program to collect, store, and display the data. The advantage of a third-party tool is that once it is set up, you can forget about it, and Performance Monitor data is automatically collected for all of your SQL Servers and stored in a SQL Server database. Also, reporting is usually built-in, so you can produce reports in seconds. The only real downside is the initial cost of the product, but that can easily be made up from the time savings it produces.

No matter which method you choose to track Performance Monitor data for your SQL Servers, you need to do something. Only by watching performance over time will you be able to gauge what is happening and to plan for the future.