Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 9. Viewing Server Performance with PerfMon and the PAL Tool

WHAT'S IN THIS CHAPTER

• When and how to use Perfmonance Monitor

• How to baseline server performance

• Using sample thresholds to identify performance problems

• Automating data analysis with Performance Analysis for Logs (PAL)

Performance Monitor, widely known as PerfMon, is a Windows tool for measuring real-time server performance and recording system performance to a file over time. Understanding when and how to use Performance Monitor enables you to quickly and effectively narrow the scope of problems from entire IT systems to specific servers, components, or resources. When used effectively, Performance Monitor can assist with resolving complex problems, some of which may seem random or intermittent, by narrowing the focus to the root cause.

Knowing which PerfMon counters to include for a given scenario will enable you to interpret the data and draw useful conclusions, following these with constructive recommendations for remediation. Capturing too much, too little, or the wrong type of data can lead you to draw no conclusion or an inaccurate conclusion, which may be then followed by incorrect recommendations. Bad or missing performance data might mean you miss correlations or patterns in the data that could support a hypothesis regarding the problem's cause.

Engineers or DBAs unfamiliar with PerfMon often look at the data generated in the performance log and ask themselves questions like the following:

• Is value X acceptable? ...