What is an execution plan?

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted.

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated execution plans, in either graphical, text or XML for ­mat, and to understand the basics of how to interpret them. In order to do this, we’ll cover the following topics:

• A brief backgrounder on the query optimizer – execution plans are a result of the optimizer’s calculations so it’s useful to know at least a little bit about what the optimizer does, and how it works.
• Actual and Estimated execution plans – what they are and how they differ
• Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical, text and XML execution plans for a very basic SELECT query
• Automating execution plan capture – using the SQL Server Profiler tool