Using showplan

The showplan option allows you to review the optimization plan the server intends to use for your query.

set showplan on
go

Once you have done that, you will have changed the characteristics of your session. Now, whenever SQL Server executes a query, it returns the optimization plan along with the results set.

---

Tip:  You need to set the showplan option on in a separate batch before executing the query. Consider this example:

/* WARNING: Don’t do this. It won’t work! */
set showplan on
select count(*)
from sales
where title_id like “A%”
set showplan off

Earlier we discussed the sequence of steps each query undergoes. That sequence is actually performed at the batch level, so first the batch is parsed; then the batch is optimized, compiled, and executed. That means that no execution takes place until the entire batch is optimized. At optimization time, when showplan would be relevant, it has not been set. When it is finally set during execution, there is no optimization to report on. Try it!

---

Here’s the optimization plan returned by the server when we executed the sample query:

STEP 1
The type of query is SELECT
Scalar Aggregate
FROM TABLE
sales
Nested iteration
Using Clustered Index
STEP 2
The type of query is SELECT

First of all, the query took two steps because all queries including aggregates (count, max) take two steps, one to build the aggregate, the second to scan it and return the aggregate data. We’re interested in STEP 1. We’ll look at it in detail.

The type of query is SELECT

The server always reports the type of query. This is particularly useful when you submit a long batch consisting of several queries. SQL Server reports the optimization plan for each statement in turn. This element will help you isolate the command you are interested in.

Scalar Aggregate

This particular query is a scalar aggregate (an aggregate without a GROUP BY clause). If a GROUP BY clause had been included, it would be a vector aggregate.

FROM TABLE
sales

When a query includes multiple tables, SQL Server reports the names of the tables in the order in which they are accessed. Join order turns out to be very important to performance, especially in large queries. In Day 21 you find out how you can help the performance of the server by looking closely at join order and multitable joins.

Nested iteration

This is the most common method of walking a table. The other method you are likely to see is GETSORTED, which occurs when the output needs to be sorted. Nested iteration is faster.

Using Clustered Index

In this last line is the real point of this exercise. SQL Server used the clustered index to resolve the query.

---

Tip:  Here’s another tip about showplan. Often you will combine it with noexec in order to review an optimization plan without needing to wait for the query to execute. This is most common when you are testing queries for a large table or with a large result set. To set the noexec option, type

set noexec on

It’s very important that you get the sequence correct; the noexec keyword will shut off all query execution for your session, so you want to execute it after showplan:

set showplan on
set noexec on

Once you have executed this, the server will report the optimization of every query you send, but the only statement SQL Server will execute is

set noexec off

One more thing: Never run any system stored procedures from a connection where you have set the showplan option. You will be stunned at the amount of information it returns. Instead, when using showplan to analyze queries, open another query session and use that session to execute stored procedures.

---