by Paul Bertucci
IN THIS CHAPTER
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.
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.