Chapter 36 -- Solving Common Performance Problems

3 4

Chapter 36

Throughout this book, you've learned about tools you can use and parameters you can adjust to help you find and solve certain performance problems. For example, in the preceding chapter, you learned how to identify problems with your SQL statements and stored procedures and how to tune those statements and procedures for optimal performance. This chapter is designed to help you easily find the information you need to solve various types of performance problems. It reviews some of the topics related to performance that were covered in other chapters, provides references to earlier chapters in which performance issues were examined, and presents some additional information about performance monitoring and system tuning.

We'll start by briefly reviewing the definition of "bottleneck." Then we'll look at how to use Microsoft Windows 2000 System Monitor (called Performance Monitor in Microsoft Windows NT) and Microsoft SQL Server Enterprise Manager to determine whether a performance problem exists. Next we'll look at how to solve a number of common performance problems, which occur at several levels, including the application level, the SQL Server level, operating system level, and the hardware level. The rules of thumb used for system capacity planning that were described in Chapter 6 are reviewed in this chapter because you can use them to analyze an existing system to determine whether you need additional hardware to improve performance. Finally we'll review several SQL Server configuration parameters, described in previous chapters, that you can adjust to change the way your system performs.

By the end of this chapter, you should be able to identify a performance bottleneck and determine its cause. You can't always solve performance problems, but most are solvable if you have the time and resources to deal with them.