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.
End of Article
Prev. page
1
[2]
next page -->
