Chapter 2: Optimizing the Performance of Queries

Microsoft Exam Objectives Covered in this Chapter:

• Troubleshoot and maintain query performance.

  • Identify poorly performing queries.

  • Analyze a query plan to detect inefficiencies in query logic.

  • Maintain and optimize indexes.

  • Enforce appropriate stored procedure logging and output.

• Troubleshoot concurrency issues.

As a continuation of Chapter 1, “Optimizing the Performance pof Databases and Database Servers,” you’ll now turn your attention to specifically optimizing the performance of queries within the SQL Server 2005 environment.

The DBA often has to optimize the performance of queries in response to users of the database solution complaining about slow performance or unresponsive systems. However, this does not necessarily mean that the root cause has to do with poorly written queries or poor indexing strategies.

It is important to appreciate the concurrent nature of SQL Server 2005 and realize that the problem might have to do with concurrency or contention instead. Consequently, you’ll look at troubleshooting concurrency in this chapter as well.