Certain details of SQL Server 2005 query plans can be viewed only in XML versions
of the plans, which you can obtain by using the XML Showplan feature. I showed
you examples of XML Showplan output in "Use Missing-Index Groups for Query Tuning,"
April 2007, InstantDoc ID 95220. Let's continue that discussion by examining
additional examples of query-plan details that can be found only in a plan's
XML output and a tool that can make working with XML query plans a pleasure.
On an unrelated note, this issue marks the 100th issue of SQL Server Magazine.
I reminisce about my longtime involvement as a contributor on
page 40.
Estimated vs. Actual Plans
To highlight query details that are available only in XML plans, we'll need
a table that's a bit larger than the largest table in the sample AdventureWorks
database. Run the script in Listing 1 to
create a copy of the Sales.SalesOrderHeader table, called dbo.OrderHeader, that's
10 times as large. Note that when submitting a query through the query window
in SQL Server Management Studio (SSMS), you can include a number after the GO
batch separator. Listing 1 includes GO 10
to indicate that the INSERT should be executed 10 times, to obtain a table that's
10 times as large as the original. The script also creates a copy of the Sales.Customer
table in the dbo schema.
Even when using XML Showplan, sometimes you don't see all the query-plan information
that you might expect to, which could happen because only certain information
is available when a query is first compiled and optimized. A query plan that's
displayed when you haven't actually executed the query is called an estimated
plan. A query plan that's displayed when you're running the query is called
an actual plan. To generate an estimated plan, you can use any of these
XML Showplan options: SET SHOWPLAN_TEXT, SET SHOWPLAN_, or SET SHOWPLAN_XML.
To generate an actual plan, use either of these options: SET STATISTICS PROFILE
or SET STATISTICS XML.
Actual-Plan Example
The first sample query illustrates two different features that appear in the
XML plan for an actual plan. Run the script in Listing
2, which first enables SHOWPLAN_XML and runs a query, then enables STATISTICS
XML and runs the same query. I advise you to use SSMS's Query Window and choose
the option to display your results in grid mode. As I mentioned last month,
if you display your results in text mode, the XML output contains the entire
XML document in a single string in your results window. In grid mode, the results
window gives you a link that you can click to open the formatted XML document
in another window.
The output from the script in Listing 2
should provide two XML links, in addition to the output generated from the second
query. Click each link, then compare their XML output. The outputs should be
similar but not exactly the same. In particular, near the top of the document
produced by SET STATISTICS XML, you should see something like the XML snippet
in Figure 1.
This query's plan indicates that SQL Server will join these two tables by performing
a hash join and a hash aggregation using multiple processors. A graphical Showplan
would show you this basic information. However, a graphical Showplan wouldn't
show you how many processors were actually used to process the query. The graphical
Showplan displays icons to show parallelism, but the only way to determine how
many processors were used during query execution is to examine the actual XML
plan. We can see from the DegreeOfParallelism attribute in the XML snippet
in Figure 1 that for my query, four processors
were used. (For more information about using the DegreeofParellism attribute,
see the sidebar "Help with Examining ParallelQuery Plans.")
You might be aware that when SQL Server performs a hash operation, whether
for a HASH JOIN, a HASH AGGREGATION, or a HASH UNION, additional memory is required
to manage the hash buckets. (For more information, about hashing, see "Hashing
for Performance," April 2002, InstantDoc ID 24024.) It isn't always obvious
how much memory is needed, though. The XML plan information provides this information
via the MemoryGrant attribute. As you can see in Figure
1, my plan needed an extra memory grant of 2,874KB to execute the query.
Although it's true that other query elements can necessitate a memory grant,
the primary explanation for a memory grant is hash operations. Since my query
uses two hashing operators, for the JOIN and GROUP BY, it's a safe bet that
the hash buckets are the reason for the extra memory requirement.
Making XML Output Easier to Read
Although reading the XML plan in its own window is certainly easier than reading
it in one long string, as it would be in Text output mode, doing so still can
be awkward. Many of you probably could write your own tools for querying the
XML plan or displaying it in a useful format. However, if you aren't fluent
with XML manipulation, you'll probably find a Microsoft tool called XML Notepad
2007 helpful. You can download XML Notepad 2007 at http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f4f30fe913628&displaylang=en.
I can save my XML plans from SSMS and open them or copy and paste them in XML
Notepad. Figure 2 shows what the
output looks like in XML Notepad. You can expand or collapse each node to see
only the parts of the plan you're interested in. Figure
2 shows the first part of my plan and includes the query, the resources
used by the optimizer to compile the query, the resources needed to run it,
and information about the first operator, which is the Gather Streams parallelism
operation. This operator is the last step in the parallel query and indicates
that SQL Server should take all the separate results from the various processors
and gather them back together to produce the final results. Note that the node
for StatementSetOptions is collapsed, but if it were expanded, you could see
all the various SET options that were enabled for this query's execution.