Chapter 13

Performance Tuning T-SQL


  • Query Processing Including Tools Usage and Optimization
  • The Query Tuning Process Including Joins, Query Plans, and Indexes

Performance tuning T-SQL is interesting but also quite frequently frustrating. It is interesting because there is so much involved in tuning that knowledge of SQL Server’s architecture and internals plays a large role in doing it well. It can be frustrating when you do not have access to change the source query as it resides inside a vendor application, or when it seems that whatever optimization technique is tried, the performance issue does not seem to be resolved. Of course, knowledge alone is not sufficient without the right tools, which you learn about in this chapter. If you have tuned a query and reduced its runtime, you may have jumped up and down with excitement, but sometimes you cannot achieve that result even after losing sleep for many nights.

In this chapter, you learn how to gather the data for query tuning, the tools for query tuning, the stages a query goes through before execution, and a little bit on how to analyze the execution plan. You must understand which stages a query passes through before actually being executed by the execution engine, so start with physical query processing.


SQL Server performs two main steps to produce the desired result when a query fires. As you would guess, the first step is query compilation, ...

Get Professional Microsoft SQL Server 2012 Administration now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.