show_plan

Syntax

show_plan(spid, batch_id, context_id, statement_number)

Parameters

• spid – is the process ID for any user connection.
• batch_id – is the unique number for a batch.
• context_id – is the unique number of every procedure (or trigger).
• statement_number – is the number of the current statemenmt within a batch.

Examples

• Example 1 – In this 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.

Usage

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.

See also sp_showplan in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for show_plan differ based on your granular permissions settings.