Tips on how to analyze query plans efficiently
T-SQL is a declarative querying language that lets you phrase your request in a logical English-like manner. The SQL Server database engine then processes the query. The relational engine (the query processor) uses the query optimizer to translate the logical request into a physical plan known as the query execution plan. The plan holds the instructions regarding which physical structures to access, how to access them, in what order, which join algorithms to use, whether to sort data, whether to spool data, and so on.
SQL Server gives you the means to analyze an execution plan using graphical as well as XML forms. Understanding what you see in a plan can help you determine the reasons for poor query performance and therefore is a key element in query tuning. The topic deserves much more than a single article, and fortunately there are entire books dedicated to the subject. For this article, I assume that you have some basic knowledge about query plans and are looking for the next step -- tips on how to analyze query plans efficiently.
For sample data, I used a database called Performance. The source code is available for download .
Estimated vs. actual plans
As you probably know, SQL Server Management Studio (SSMS) gives you the option to obtain both an estimated and an actual query plan. For the former, highlight the query in question and click the Display Estimated Execution Plan (Ctrl+L) button in the SQL Editor Toolbar. For the latter, click the Include Actual Execution Plan (Ctrl+M) button and execute the query. Either way, SQL Server has to create the execution plan before executing it. But with the estimated plan, SQL Server won't run the query, and for some measures (e.g., number of rows returned, number of executions of the plan iterator), SQL Server shows only estimates. With the actual plan, SQL Server will create a plan, make some runtime decisions (e.g., actual degree of parallelism -- DOP, memory grants), collect runtime information (e.g., actual numbers of rows, actual numbers of executions), and present all those along with the estimates in the actual execution plan. When you analyze query plans, you'll typically find that actual plans are much more informative and interesting. In fact, often what can help you investigate suboptimal plans is comparing estimated measures with their actual counterparts.
Let's take the following query (call it Query 1) as an example to demonstrate an actual execution plan (which Figure 1 shows):
FROM dbo.Orders
ORDER BY orderid;
Figure 1: Plan for Query 1
There are two ways to follow the logic in the plan. One is based on data flow order, which is right-to-left, top-to-bottom. The other is the order of the API calls, which actually start with the root node (left-most, top-most). The data flow order is typically the one you want to follow. So in our case, the plan uses an Index Scan iterator to scan the first 25 rows in the nonclustered index PK_Orders (defined on orderid as the key). For each of the 25 rows, the Nested Loops iterator invokes a Key Lookup iterator to look up the related data row to obtain the rest of the attributes the query needs to return. The Top iterator is the one that causes the scan to stop after 25 rows.
Sometimes though, like with our plan, if you follow data flow order, some things can be confusing. For example, how does the Index Scan iterator know to stop after 25 rows if the Top iterator that tells it to stop appears later in the plan? The answer is that the internal API calls start with the root node (SELECT iterator in our case). This root node invokes the Top iterator. The Top iterator invokes a method 25 times that asks for a row from the Nested Loops iterator. In turn, the Nested Loops iterator invokes a method 25 times that asks for a row from the Index Scan iterator. Hence, the Index Scan iterator doesn't proceed beyond the 25 first rows that it scans. In short, although it's usually more intuitive to follow data flow order to interpret the plan, understanding the API call order can help you better understand what's really going on.
