1

I need to find number of pages scanned by the operator in a sql-server query plan. I used

SET STATISTICS IO ON

This returned number of logical, physical, read-ahead pages scanned per table, but I need for every operator.

Moreover, I am unable to read the IO messages using JDBC driver programs, and I have more than 100 queries to be executed and to record number of pages read by each operator.

Is there any method to at least get number of pages per table, that can be accessed by JDBC driver programs

OR

Is there any flag kind of thing to be set to get the number of pages scanned in the XML PLAN Itself.

  • To get info and warning messages returned by the statement (i.e. the IO messages), use getWarnings. – Dan Guzman Jul 13 '18 at 10:23
  • I tried that, Its returning NULL, since its not warning message – Nawap Jul 13 '18 at 10:24
  • 1
    The messages are informational (severity 0). getWarnings returns these info messages for me with a prepared statement and the latest Microsoft JDBC driver. You might post that part of your question on Stackoverflow. – Dan Guzman Jul 13 '18 at 10:35
2

Io statistics per table was added to actual execution plans starting in SQL Server 2016. It was ported to SQL Server 2014 SP2 as well.

I executed a simple query and uploaded the query plan here. Comparing the results of SET STATISTICS IO ON:

Table 'BIG_TABLE'. Scan count 1, logical reads 921659, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SMALL_TABLE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To what's in the XML for the actual plan:

<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="6451600" ActualRowsRead="6451600" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="972" ActualCPUms="972" ActualScans="1" ActualLogicalReads="921659" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  <DefinedValues />
  <Object Database="[TEST]" Schema="[dbo]" Table="[BIG_TABLE]" IndexKind="Heap" Storage="RowStore" />
</TableScan>


<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="10" ActualRowsRead="10" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  <DefinedValues />
  <Object Database="[TEST]" Schema="[dbo]" Table="[SMALL_TABLE]" IndexKind="Heap" Storage="RowStore" />
</TableScan>

Reveals a perfect match in this case.

  • yeah, figured out that later, I didn't have good xml viewer , so i missed it out, thanks – Nawap Jul 20 '18 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.