Chapter 37. Monitoring SQL Server Performance


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.

Figure 37.1. SQL Server performance-monitoring key elements.

graphics/37fig01.gif

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.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net