Chapter 9. Performance Analysis and Tuning

IN THIS CHAPTER

• Physical Database Design, Analysis, and Tuning

• Troubleshooting Locking and Blocking

• Query Performance Tuning

• Designing Applications for Performance

• Performance Monitoring and Tuning Tools

Performance analysis and tuning is a challenging exercise that requires understanding of tools and techniques to measure and troubleshoot a performance issue. Some of the top performance killers include poor application design, inferior physical/logical database design, and costly/slow-running queries. Insufficient hardware resources and improper configurations of the operating system and the database system are some more causes of performance degradation.

SQL Server 2005 changes the performance analysis and tuning paradigm by introducing innovative features and tools that facilitate efficient physical database design, query optimization, locking and blocking avoidance, and so on. In this chapter, you will learn how to use these features and tools to proactively design, monitor, and tune a database application.

Let's begin by looking at some of the new techniques for optimizing the physical database design.