The XML Query-Plan Treasure Trove

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.

Another valuable piece of information that XML plans provide is the actual and compiled value of parameters, either for stored procedures or for prepared (or autoparameterized) queries. (For more information about autoparameterization, see "SQL Server 7.0 Plan Caching," September 1999, InstantDoc ID 5915.) Let's look at an example using a stored procedure. You're probably aware that stored procedures are optimized and compiled the first time they're executed and that the initial plan continues to be used for subsequent executions as long as the plan stays in cache. Listing 3 builds a nonclustered, nonunique index on the SalesPersonID column of my OrderHeader table, then creates a stored procedure to return rows with a specific value for SalesPersonID. SQL Server will compile a plan for the procedure based on the first parameter I pass, so I'll first call the procedure with a parameter that returns 160 rows out of 314,650. Because the procedure will return so few rows, the optimizer should decide to use the nonclustered index. I use the following statement to generate the XML plan:

SET STATISTICS XML ON;  EXEC GetSales 288;  GO

Your XML plan should include a section near the bottom that shows you the parameter list. It includes both the compiled and runtime values. Since this is the first time we've executed the procedure, the two values are the same.

If I now run the procedure again, with an actual parameter value of 277, SQL Server will use the same plan for the procedure that it already has available. You might notice that the query takes quite a bit longer to run, and in fact, the plan is nonoptimal for a query that returns 4,730 rows. I use this statement to generate the plan:

SET STATISTICS XML ON;  EXEC GetSales 277;  GO

If I find that a stored procedure seems to be performing well with some parameters and less well with others, I can look at the XML plan to determine what parameters the optimizer is assuming. In this case, I'll see the information for the ParameterList node. In other words, I'll be able to see the value that the stored procedure was compiled with, which should give me a big clue about why the execution might not be as fast as it could be.

More-Detailed Query-Plan Information

XML query plans can be invaluable for anyone, regardless of your fluency with XML. I find that I now prefer to look at my plans in XML format if I need more detail than simply knowing what index was used. You can also use XML format to share query plans with others, such as remote colleagues or support providers. Even if you prefer the graphical Showplan format, XML can still come in handy. If you look at a plan in graphical format in SSMS, you can rightclick the plan, choose to Save Execution Plan As, and supply a filename and location. Although the default suffix for the saved plan will be .sqlplan, the plan will actually be saved as an XML plan. The nicest feature of plans saved with this suffix is that when you try to open them, they'll open in SSMS in the graphical format. Anyone with SSMS can display a plan this way, even if they don't have your database or data to re-execute the same query.