O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

13.4. Performance Monitor

Performance Monitor is an important tool because not only does it enable you to know how SQL Server is performing, it is also the tool that indicates how Windows is performing. Performance Monitor provides a huge set of counters. Probably no one understands all of them, so don't be daunted.

This section is not about how to use Performance Monitor (although later in this section you will learn about two very valuable tools, Logman, and Relog, that make using Performance Monitor a lot easier in a production environment). The focus in this chapter is on how you can use the capabilities of this tool to diagnose performance problems in your system. For general information about using Performance Monitor, look at the Vista or Windows Server 2008 documentation.

As mentioned, you need to monitor three server resources:

  • CPU

  • Memory

  • I/O (primarily disk I/O, but in some cases network as well)

Monitor these key counters over a "typical" 24-hour period. Pick a typical business day, not a weekend or holiday, so you get an accurate picture of what's happening. You should also take into account any specific knowledge of your business and monitor for peaks of activity such as end of week, end of month, or other special activities.

13.4.1. CPU Resource Counters

Several counters show the state of the available CPU resources. Bottlenecks due to CPU resource shortages are frequently caused by problems such as more users than expected, one or more users running very expensive ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required