show_execio_xml includes diagnostic information that can be helpful for investigating problematic queries. The information show_execio_xml displays includes:
The version level of the query plan. Each version of the plan is uniquely identified. This is the first version of the plan:
<planVersion>1.0</planVersion>
The statement number in a batch or stored procedure, along with the line number of the statement in the original text. This is statement number 2, but line number 6, in the query:
<statementNum>2</statementNum> <lineNum>6</lineNum>
The abstract plan for the query. For example, this is the abstract plan for the query select * from titles:
<abstractPlan> <![CDATA[> ( i_scan titleidind titles ) ( prop titles ( parallel 1 ) ( prefetch 8 ) ( lru ) ) ]]> </abstractPlan>
The logical IO, physical IO, and CPU costs:
<costs> <lio> 2 </lio> <pio> 2 </pio> <cpu> 18 </cpu> </costs>
You can estimate the total costs with this formula:
25 X pio + 2 X lio + 0.1 X cpu
The estimated execution resource usage, including the number of threads and auxiliary scan descriptors used by the query plan.
The number of plans the query engine viewed and the plans it determined were valid, the total time spent in the query engine (in milliseconds), the time it took to determine the first legal plan, and the amount of procedure cache used during the optimization process.
<optimizerMetrics> <optTimeMs>6</optTimeMs> <optTimeToFirstPlanMs>3</optTimeToFirstPlanMs> <plansEvaluated>1</plansEvaluated> <plansValid>1</plansValid> <procCacheBytes>140231</procCacheBytes> </optimizerMetrics>
The last time update statistics was run on the current table and whether the query engine used a hard-wired estimation constant for a given column that it could have estimated better if statistics were available. This section includes information about columns with missing statistics:
<optimizerStatistics> <statInfo> <objName>titles</objName> <columnStats> <column>title_id</column> <updateTime>Oct 5 2006 4:40:14:730PM</updateTime> </columnStats> <columnStats> <column>title</column> <updateTime>Oct 5 2006 4:40:14:730PM</updateTime> </columnStats> </statInfo> </optimizerStatistics>
An operator tree that includes table and index scans with information about cache strategies and IO sizes (inserts, updates, and deletes have the same information for the target table). The operator tree also shows whether updates are performed in “direct” or “deferred” mode. The exchange operator includes information about the number of producer and consumer processes the query used.
<TableScan> <VA>0</VA> <est> <rowCnt>18</rowCnt> <lio>2</lio> <pio>2</pio> <rowSz>218.5555</rowSz> </est> <varNo>0</varNo> <objName>titles</objName> <scanType>TableScan</scanType> <partitionInfo> <partitionCount>1</partitionCount> </partitionInfo> <scanOrder> ForwardScan </scanOrder> <positioning> StartOfTable </positioning> <dataIOSizeInKB>8</dataIOSizeInKB> <dataBufReplStrategy> LRU </dataBufReplStrategy> </TableScan>