Professional Microsoft SQL Server 2012 Administration

Chapter 13

Performance Tuning T-SQL

WHAT’S IN THIS CHAPTER

• 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.

PHYSICAL QUERY PROCESSING PART ONE: COMPILATION AND RECOMPILATION

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, ...