Usage scenarios

Scenario A

To send the execution plan XML to the client as trace output, use:

set plan for show_exec_xml to client on

Then run the queries for which the plan is wanted:

select id from sysindexes where id < 0

If dbcc traceon(3604) is set, trace information goes to the client’s connection. If dbcc traceon (3605) is set, trace information goes to the error log.

Scenario B

To get the execution plan, use the showplan_in_xml built-in. You can get the output from the last query, or from any of the first 20 queries in a batch or stored procedure.

set plan for show_opt_xml to  message on

Run the query as:

select id from sysindexes where id < 0
select name from sysobjects where id > 0
go

select showplan_in_xml(0)
go

The example generates two XML documents as text streams. You can run an XPath query over this built-in as long as the XML option is enabled in Adaptive Server.

select xmlextract("/", showplan_in_xml(-1))
go

This allows the XPath query “/” to be run over the XML doc produced by the last query.

Scenario C

To set multiple options:

set plan for show_exec_xml, show_opt_xml to client on
go

select name from sysobjects where id > 0
go

This sets up the output from the optimizer and the query execution engine to send the result to the client, as is done in normal tracing.

set plan for show_exec_xml off
go
select name from sysobjects where id > 0
go

The optimizer’s diagnostics are still available, as show_opt_xml is left on.

Scenario D

When running a set of queries in a batch, you can ask for the optimizer plan for the last query.

set plan for show_opt_xml to message on
go
declare @v int
select @v = 1
select name from sysobjects where id = @v
go

select showplan_in_xml(-1)
go

showplan_in_xml() can also be part of the same batch as it works the same way. Any message for the showplan_in_xml() built-in is ignored for logging.

To create a stored procedure:

create proc PP as
declare @v int
select @v = 1
select name from sysobjects where id = @v
go

exec P
go

select showplan_in_xml(-1)
go

If the stored procedure calls another stored procedure, and the called stored procedure compiles, and optimizer diagnostics are turned on, you get the optimizer diagnostics for the new set of statements as well. The same is true if show_execio_xml is turned on and only the called stored procedure is executed.

Scenario E

To query the output of the showplan_in_xml() for the query execution plan, which is an XML doc:

set plan for show_exec_xml to message on
go

select name from sysobjects
go

select  case when
'/Emit/Scan[@Label=“Scan:myobjectss”]' xmltest
showplan_in_xml(-1)
then “PASSED” else "FAILED" end
go

set plan for show_exec_xml off
go

Scenario F

Use show_final_plan_xml to configure Adaptive Server to display the query plan as XML output. This output does not include the actual LIO costs, PIO costs, or the row counts. Once show_final_plan_xml is enabled, you can select the query plan from the last run query (which is query ID of -1). To enable show_final_plan_xml:

set plan for show_final_plan_xml to message on

Run your query, for example:

use pubs2
go
select * from titles
go

Select the query plan for the last query run using the showplan_in_xml parameter:

select showplan_in_xml(-1)