Chapter 37. Monitoring SQL Server Performance

by Paul Bertucci


  • Performance-Monitoring Approach

  • Performance Monitor

  • Windows Performance Counters

  • SQL Server Performance Counters

  • SNMP Support

  • Using DBCC to Examine Performance

  • Other SQL Server Performance Considerations

No SQL Server implementation will be perfect "out of the box." As you build and add SQL Server applications to your server, you will want to start taking an active approach to monitoring performance. You will also need to keep re-evaluating things as more and more load is placed on your servers. This chapter will focus on SQL Server monitoring and leave the other types of servers for those specialists. Other types of servers would include application servers, backup servers, domain controllers, file and print servers, mail/messaging servers, and Web servers.

You can monitor many things on your SQL Server platform, ranging from physical and logical I/O to network packets being handled by the server. To make this monitoring task a little cleaner, this chapter has classified the key monitoring handles into network, processors, memory/cache, and disk systems. Figure 37.1 shows how these key elements interrelate with SQL Server 2000 and Windows. The aspect of utilization will be at the center of most of the discussions in this chapter, whether it is something like CPU utilization, memory utilization, or something else. The important concept to remember will be how to monitor or measure utilization and how to make changes to improve this utilization because you are still not in a perfect world of infinite CPU power, infinite disk space, infinite network load capability, and infinite memory.

Figure 37.1. SQL Server performance-monitoring key elements.


It will also be essential to know which tools you can use to get this valuable information. The tools you can use include Windows NT/2000 Performance Monitor with various counters, SQL Servers DBCC, and even a variety of SQL Server system-stored procedures. Many other third-party products are also available that you might already have in-house.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features