Microsoft® SQL Server® 2008 Bible

Chapter 55. Performance Monitor

IN THIS CHAPTER

• Observing server performance

• Saving counter logs

When I tuned the search feature in Nordic (my T-SQL based O/R DBMS) I pushed the database up to five million objects by running several instances of Query Editor, each running a loop to generate random names using the distribution of names from the 1990 U.S. Census. To be as efficient as possible, I played with the number of Query Editor instances and found that, on my machine, about ten instances with a .025-second pause produced the highest number of transactions per second.

The tool I used to watch the transactions per second, and the number of objects in the database, was PerfMon.

Performance Monitor, or "PerfMon," has been around for quite a while. Anyone working with Windows as an IT platform is familiar with PerfMon. These are the first tools used for high-level diagnostics and health of any server.

SQL Server extends PerfMon by adding dozens of SQL Server–specific counters. While PerfMon alone doesn't provide enough detail to fully diagnose SQL Server, it does a great job of illustrating the overall server performance issues and highlighting SQL Server themes.

PerfMon is more than just a pretty face. PerfMon's counter logs can write data to a binary perflog (*.blg) file or to a comma-delimited file (universal across Windows versions). Either type of file can be integrated into SQL Server Profiler.

All in all, anyone working with SQL Server needs to be proficient with PerfMon.

Using Performance ...