0

I'm reviewing a stored procedure that's been a performance problem. And I just realized that some of the tables which are used in the stored procedure don't even show up AT ALL in the results from Set Statistics I/O?

Is this normal, or a bug? It's a little disturbing because I use the results of Set Statistics I/O a lot, when figuring out what to index.

The tables that don't show up in the output of Set Statistics I/O are in a subselect on the main query, like the ActivityFeedCache table below:

SELECT *
FROM (
    SELECT AF.ActivityFeedID
        ,A.ActivityType AS ActivityType
        ,A.IconClass
        ,AF.SubscriptionID
        ,AF.ObjectID
        ,AF.ObjectType
        ,(
            SELECT L.Locale
                ,AFC.LocalizedMessage
            FROM ActivityFeedCache AFC
            JOIN Locales L
                ON AFC.LocaleID = L.LocaleID
            WHERE ActivityFeedID = AF.ActivityFeedID
            FOR JSON PATH
            ) AS MessageJSON
    FROM ActivityFeed AF
    ETC 
    ETC    
    ) AS Results    

This is SQL Server 2017 (RTM-CU13).

EDIT: I've narrowed down to a small query that shows a missing table in the Statistics IO output. The Locales table is missing in Statistics I/O, but shows up in the execution plan. Here it is:

-- Locales table doesn't show in statistics i/o output
SELECT AF.ActivityFeedID
        ,(
            SELECT L.Locale
                ,AFC.LocalizedTeamMessage AS LocalizedMessage
            FROM ActivityFeedTeamCache AFC
            JOIN Locales L
                ON AFC.LocaleID = L.LocaleID
            WHERE ActivityFeedID = AF.ActivityFeedID
            FOR JSON PATH
            ) AS TeamMessageJSON
    FROM ActivityFeed AF
Where AF.ActivityFeedID = (select top 1 ActivityFeedID from ActivityFeed)

Here's the Statistics I/O output:


(1 row affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityFeedTeamCache'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityFeed'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And here's a small chunk of the actual execution plan XML, just so you can see the Locales table is actually being used:

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Locales]" Alias="[L]" Column="Locale" />
      </DefinedValue>
    </DefinedValues>
    <Object Database="[Database1]" Schema="[dbo]" Table="[Locales]" Index="[PK_Locales]" Alias="[L]" IndexKind="Clustered" Storage="RowStore" />
    <SeekPredicates>
      <SeekPredicateNew>
        <SeekKeys>
          <Prefix ScanType="EQ">
            <RangeColumns>
              <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Locales]" Alias="[L]" Column="LocaleID" />
            </RangeColumns>
            <RangeExpressions>
              <ScalarOperator ScalarString="[Database1].[dbo].[ActivityFeedTeamCache].[LocaleID] as [AFC].[LocaleID]">
4
  • Really hard for us to guess without the actual stats i/o output and the actual execution plan, but a couple of thoughts here: sqlperformance.com/2012/10/t-sql-queries/beware_statistics_io Oct 8 '20 at 17:03
  • Thanks Aaron, I just added some details that will hopefully be useful.
    – Sylvia
    Oct 10 '20 at 16:25
  • Does that specific query you've added return any rows? It might not have had to read from that table (its presence in the execution plan does not necessarily mean it will actually be accessed). See if in the actual plan the executions are zero (or if that operator is dimmed when generating an actual plan in Plan Explorer - see "Apply opacity for operators with 0 executions + actual rows" here). Oct 10 '20 at 16:39
  • Ah-ha! You put your finger on it. I didn't look at the number of executions in the actual execution plan, and they were indeed zero with the query given. Once I changed the ActivityFeedID to a value that exists in both tables, the Locales table showed up in the Statistics I/O output. Thank you! If you'd like put your comment in as an answer, I'd be happy to mark it as correct.
    – Sylvia
    Oct 11 '20 at 15:13
1

There is a scenario where an estimated execution plan can contain an operator that doesn't get used at runtime, for example when a predicate renders the operator a no-op.

It's tricky to spot because there aren't many clues it's happened, other than the operator's actual execution count is 0. I helped drive a change in SentryOne Plan Explorer that makes it more obvious - the operator icon gets dimmed. You can read about this change in this blog post under the heading, "Apply opacity for operators with 0 executions + actual rows."

If a table is only touched by a single operator in the execution plan, and that operator gets eliminated at runtime (meaning the table does not get touched), that table will not show up in Statistics IO.

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.