Retrieves the query plan for a specified server process (the target process) and a SQL statement. This function is called several times by sp_showplan because a built-in function can return just one value per call, but sp_showplan must return several values to the client.
show_plan(spid, batch_id, context_id, statement_number)
is the process ID for any user connection.
is the unique number for a batch.
is the unique number of every procedure (or trigger).
is the number of the current statemenmt within a batch.
In the following example, show_plan performs the following:
Validates parameter values that sp_showplan cannot validate. -1 is passed in when the user executes sp_showplan without a value for a parameter. Only the spid value is required.
If just a process ID is received, then show_plan returns the batch ID, the context ID, and the statement number in three successive calls by sp_showplan.
Find the E_STMT pointer for the specified SQL statement number.
Retrieves the target process’s query plan for the statement. For parallel worker processes the equivalent parent plan is retrieved to reduce performance impact.
Synchronizes access to the query plan with the target process.
if (@batch_id is NULL) begin /* Pass -1 for unknown values. */ select @return_value = show_plan(@spid, -1, -1, -1) if (@return_value < 0) return (1) else select @batch_id = @return_value select @return_value = show_plan(@spid, @batch_id, -1, -1) if (@return_value < 0) return (1) else select @context_id = @return_value select @return_value = show_plan(@spid, @batch_id, @context_id, -1) if (@return_value < 0) return (1) else begin select @stmt_num = @return_value return (0) end end
As the example shows, call show_plan three times for a spid :
The first returns the batch ID
The second returns the context ID
The third displays the query plan, and returns the current statement number.
For a statement that is not performing well, you can change the plans by altering the optimizer settings or specifying an abstract plan.
When you specify the first int variable in the existing show_plan argument as “-”, show_plan treats the second parameter as a SSQLID.
A single entry in the statement cache may be associated
with multiple, and possibly different, SQL plans. show_plan
displays only one of them.
ANSI SQL – Compliance level: Transact-SQL extension.
