Showing cached plans in XML

show_cached_plan_in_xml helps track the query performance in the statement cache. For a given query, show_cached_plan_in_xml, identified by its object ID or SSQLID and PlanID, returns:

• The header section, which contains information about the cache statement, such as the statement ID, object ID and the text:

  <?xml version="1.0" encoding="UTF-8"?>
  <query> 
      <statementId>1328134997</statementId> 
  <text> 
  <![CDATA[SQL Text: select name from sysobjects where id = 10]]>
  </text>
  

  If PlanID is set to 0, show_cached_plan_in_xml displays output for all available plans associated with the cached statement.

• The plan section which contains the plan ID and these subsections:

  • Parameter – returns the plan status, the parameters used to compile the query, and the parameter values that caused the slowest performance.

  •     <planId>11</planId>
        <planStatus> available </planStatus>
        <execCount>1371</execCount> 
        <maxTime>3</maxTime> 
        <avgTime>0</avgTime> 
        <compileParameters/> 
        <execParameters/>
    

  • opTree – returns the operators tree, row count, and logical I/O (lio) and physical I/O (pio) estimates for every operator. the opTree sub-section returns query plan and optimizer estimates such as lio, pio and row count

    This is an example of an output for the Emit operator.

        <opTree> 
            <Emit>
            <VA>1</VA>
            <est>
                <rowCnt>10</rowCnt>
                <lio>0</lio>
                <pio>0</pio>
                <rowSz>22.54878</rowSz>
            </est>
            <act>
                <rowCnt>1</rowCnt>
            </act>
            <arity>1</arity>
                <IndexScan>
                    <VA>0</VA>
                    <est>
                        <rowCnt>10</rowCnt>
                        <lio>0</lio>
                        <pio>0</pio>
                        <rowSz>22.54878</rowSz>
                    </est>
                    <act>
                        <rowCnt>1</rowCnt>
                        <lio>3</lio>
                        <pio>0</pio>
                    </act>
                    <varNo>0</varNo>
                    <objName>sysobjects</objName>
                    <scanType>IndexScan</scanType>
                    <indName>csysobjects</indName>
                    <indId>3</indId>
                    <scanOrder> ForwardScan </scanOrder> 
                    <positioning> ByKey </positioning> 
                    <perKey>
                        <keyCol>id</keyCol>
                        <keyOrder> Ascending </keyOrder> 
                    </perKey>
                    <indexIOSizeInKB>2</indexIOSizeInKB> 
                    <indexBufReplStrategy> LRU </indexBufReplStrategy> 
                    <dataIOSizeInKB>2</dataIOSizeInKB> 
                    <dataBufReplStrategy> LRU </dataBufReplStrategy> 
                 </IndexScan>
            </Emit> 
        <opTree>
    

  • execTree – returns the query plan with the operator internal details. Details vary, depending on the operator. This is an example of an output for the Emit operator.

        <Emit>
        <Details>
        <VA>5</VA>
            <Vtuple Label="Output Vtuple">
            <collection Label="Columns (#2)">
            <Column>
            <0x0x1462d2838) type:GENERIC_TOKEN len:0 offset:0 valuebuf:0x(nil) status:(0x00000008 (STATNULL)) (constant:0x0x1462d24c0 type:INT4 len:4 maxlen:4 constat: (0x0004 (VARIABLE), 0x0002 (PARAM)))
            </Column>
            <Column>
            (0x0x1462d2878) type:GENERIC_TOKEN len:0 offset:0 valuebuf:0x(nil) status:(0x00000008 (STATNULL)) (constant:0x0x1462d26e8 type:INT4 len:4 maxlen:4 constat: (0x0004 (VARIABLE), 0x0002 (PARAM))
            </Column>
            <Collection>
            <Collection Label="Evals">
            <EVAL>
            constp: 0x0x1462d2290 status: 0 E_ASSIGN
            </EVAL>
            <EVAL>
            constp: 0x0x1462d2348 status: 0 E_ASSIGN
            </EVAL>
            <EVAL>
            constp: 0x(nil) status: 0 E_END
            </EVAL>
            </Collection>
            </Vtuple>
        </Details>