Provides the history of the query plans for recently executed queries. monSysPlanText returns one row of text from each line of the running query plans (similar to what is returned sp_showplan or by set showplan on).
To make sure monSysPlanText reads the query plan text in the correct sequence, order the query result by SequenceNumber. For queries returning data for multiple queries or processes, order the query result by SPID, KPID, BatchID, SequenceNumber.
Enable the enable monitoring, plan text pipe max messages, and plan text pipe active configuration parameters for this monitoring table to collect data.
Typically, there are multiple rows in this table for each query plan. Arrange the rows by sorting on the SequenceNumber column in ascending order.
monSysPlanText is a historical monitoring table. See Stateful Historical Monitoring Table in the Performance and Tuning Guide.
The columns for monSysPlanText are:
Name |
Datatype |
Attributes |
Description |
|---|---|---|---|
PlanID |
int |
Unique identifier for the plan. |
|
InstanceID |
int |
(Cluster environments only) ID of an instance in a shared-disk cluster. |
|
SPID |
smallint |
Session process identifier. |
|
KPID |
int |
Kernel process identifier. |
|
BatchID |
int |
Unique identifier for the SQL batch for which the plan was created. |
|
ContextID |
int |
The stack frame of the procedure, if a procedure. |
|
SequenceNumber |
int |
A monotonically increasing number indicating the position of the PlanText column within the entire plan text. |
|
DBID |
int |
Unique identifier for the database where the procedure is stored, if the plan is for a stored procedure. |
|
ProcedureID |
int |
Unique identifier for the procedure, if the plan is for a stored procedure. |
|
DBName |
varchar(30) |
Null |
Name of the database in which the statement represented by this plan is executed. This column is NULL if this database is not open when monSysPlanText is queried. If the process is executing a stored procedure or other compiled object, the database name is the name of the database for that object. |
PlanText |
varchar(160) |
Null |
Plan text output. |