concept execution plan in category sql server

This is an excerpt from Manning's book Learn Windows SQL Server Administration in a Month of Lunches. Login to get full access to this book.

If you have a specific query, and want to see if SQL Server is using indexes to execute that query, you can manually run the query in SQL Server Management Studio and look at the execution plan SQL Server came up with. I’ll show you how to do that in chapter 12.

When SQL Server is given a query to execute, it runs the query through an internal query optimizer. The optimizer looks at what the query is trying to do, what indexes are available to help, what condition those indexes are in, what load the server is currently handling, literally dozens of different factors. It considers all the ways it might execute the query—scanning a table, seeking in an index, and so on. There’s a limit on how long it’ll run through the permutations, because eventually it’ll be wasting time instead of running the query! At some point it’ll pick the best choice found by then, and that becomes the execution plan for that query. SQL Server then runs the plan, executing the query.

Now let’s see the execution plan SQL Server came up with. We’ll start by asking SQL Server to display the estimated plan; this submits our query to the optimizer, but doesn’t run the query. The estimated plan is usually close to the final plan, but there are a few things SQL Server doesn’t take into account. We’ll compare the actual plan in a bit. For now, in SQL Server Management Studio, click the Query menu and select Display Estimated Execution Plan (or press Ctrl+L). Figure 12.2 shows what you should see.

Figure 12.2. The estimated execution plan is in the bottom pane.

We’ll spend some time interpreting this output in the next section; for now, let’s see the actual execution plan, too. From the Query menu again, select Include Actual Execution Plan (or press Ctrl+M). This time, you’ll also have to run the query to get the execution plan. Figure 12.3 shows what I got; your results should be similar if not identical.

Figure 12.3. You have to run the query to get the actual execution plan.

You’ll notice that the query results show up in their own tab, separate from the execution plan. This actual plan looks a lot like the estimated one, which is common, especially for simple queries running against a small set of tables. You’ll also notice a textual hint above the graphical plan: Missing Index (Impact 87.844). That’s SQL Server’s way of saying, “Hey, I could have run this a lot faster—maybe 87% faster—if this other index had existed.” Take those tips into consideration, but don’t let them freak you out. An index that would speed up this particular query might slow down many, many others, so you have to put things into context. For example, this plan took a couple of milliseconds to run. A savings of 87% would still be a couple of milliseconds—87% seems like a big potential savings, but it’s 87% of a tiny number, so any improvement will be pretty tiny, too.

Analyze the execution plan on the following query (which uses the AdventureWorks-2012 database):

SELECT  Sales.SalesOrderDetail.OrderQty ,
Sales.SalesOrderDetail.SpecialOfferID ,
Sales.SalesOrderDetail.UnitPrice ,
Sales.SalesOrderDetail.UnitPriceDiscount ,
Sales.SalesOrderHeader.Status ,
Sales.SalesOrderHeader.AccountNumber
FROM    Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID =
     Sales.SalesOrderHeader.SalesOrderID
ORDER BY Sales.SalesOrderDetail.UnitPrice DESC

copy

See if you can now answer a few questions about the execution plan:

12.3. How to read an execution plan

First, remember your goal when reading an execution plan: you might not be able to fix anything! You’re trying to spot bad queries or operations that indicate there’s an operational or design problem. You’ll typically need to report these to a vendor or a developer. You’re diagnosing the problem, even though you may not be able to fix the problem all by yourself.