Chapter 7: Optimizing SQL Server 2005 Performance

Overview

Database optimization is a compelling part of the database developer's job role. The difference in performance between a well-optimized database and a non-optimized database can be enormous. Working with optimization demands a keen eye for details and an interest in understanding how the database engine works. The more you learn about what Microsoft SQL Server actually needs to do to complete a task, the greater your chance will be to successfully optimize the database. In a sense, you must try to "become" SQL Server.

The task of optimizing a database can be broken down into subtasks, including: optimizing queries and database routines, creating appropriate indexes, and normalizing and de-normalizing the database. (Database normalization is beyond the scope of this training kit.)

Exam objectives in this chapter:

• Optimize and tune queries for performance.

  • Evaluate query performance.

• Analyze query plans.

• Modify queries to improve performance.

• Test queries for improved performance.

  • Detect locking problems.

  • Modify queries to optimize client and server performance.

  • Rewrite subqueries to joins.

  • Design queries that have search arguments (SARGs).

  • Convert single-row statements into set-based queries.

• Optimize indexing strategies.

  • Design an index strategy.

  • Analyze index use across an application.

  • Add, remove, or redesign indexes.

  • Optimize index-to-table-size ratio.

• Optimize data storage.

  • Choose column data types to reduce storage requirements across the enterprise.

  • Design appropriate use of varchar across the enterprise.

  • Denormalize entities to minimize page reads per query.

  • Optimize table width.