Summary

Performance tuning is often an iterative process in which you identify and fix bottlenecks, apply one set of configuration changes at a time, and test and collect data to see if the application meets its performance objective, and, if not, continue with the tuning-configuration-testing process. Proactive monitoring and tuning can save significant time, money, and resources. This chapter assumes that you are somewhat familiar with SQL Server 2000 performance monitoring and tuning, and it discusses the new techniques and features that you can use to proactively design, monitor, and tune database applications to maximize the performance.

The chapter begins with a discussion on optimizing physical database design, which is an important aspect of maximizing the performance and manageability of any database system. Next, some new techniques are presented to troubleshoot locking and blocking issues. The subsequent two sections describe techniques to optimize the queries and application design. Because Chapter 5 already contains a detailed description of SQL Server 2005 tools, including performance monitoring and tuning tools, this chapter discusses only DMVs in detail.

The next few chapters introduce you to developer and business intelligence features to show you how they affect you as a DBA. Chapter 10 starts with an overview of XML and web services support in SQL Server 2005.