For the following examples, if dbcc traceon(3604) is set, trace information is sent to the client’s connection. If dbcc traceon (3605) is set, trace information is sent to the error log. For Adaptive Server versions 15.0.2 and later, you can use the set switch on. For example:
set switch on 3604 set switch on 3605
Optimization tracing options (dbcc traceon/off(302,310,317)) from versions of Adaptive Server earlier than 15.0 are no longer supported.
Use dbcc traceon(3604) or set switch on print_output_to_client to direct trace output to the client process that would otherwise go to the error log. Use dbcc traceon(3605) or set switch on print_output_to_errorlog to direct output to the error log as well as to the client process.
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
To get the execution plan, use the showplan_in_xml function. 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.
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.
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() function 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 PP 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.
To query the output of the showplan_in_xml() function 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
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 has a 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)
