Same Execution Plan different stats

• DesNorton

  SSC-Insane

  Points: 24381

  More actions

  October 19, 2018 at 10:39 am

  #2010455

  DesNorton - Thursday, October 18, 2018 1:54 PM

  anvegger - Thursday, October 18, 2018 1:45 PM

  Jeffrey Williams 3188 - Thursday, October 18, 2018 1:15 PM

  If you look at the plans - this is telling you that the actual number of rows it retrieved from the ActivityInstance is 4515 but it had to 'read' 16168215 to get that result.  On the other database the actual rows is 4429 but it only had to read 177160 rows to get that result.

  That is correct: the question is
  how
  217 136 actual records in Table ActivityInstance have been 'read' 16 168 215 times in one database (Database D) and
  219 209 actual records in the same table ActivityInstance have beed 'read' only 177 160 times in the other  (Database A) using the same index and similar statistics. 

  I don't see that logic processed from the execution plan.

  The extra reads are due to the extra records in the Schedule and ActivityType tables
  

• I may have missed something, but from the plan it appears that the 2nd largest I/O is from the table dbo.ActivityType.  However, the query does not appear to reference dbo.ActivityType.
• The largest I/O is from dbo.Schedule.  It might help to pre-filter the Schedule table in the WHERE clause by adding AND s.LicenseeId = 'app1347' Apologies.  I misread the existing where clause.
• Can we see the index definitins on these 2 tables.  That may assist in providing better joins and/or predicates.  the code below will extract the index information.SELECT
  [TableName] = t.name
  , [idxName]  = si.name
  , [idxType]  = si.type_desc
  , [isPK]   = si.is_primary_key
  , [isUNQ]   = si.is_unique
  , si.is_disabled
  , [Columns]  = LTRIM(RTRIM(
         STUFF(
         ( SELECT ',' + ac.name + CASE WHEN sic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
          FROM sys.index_columns sic
          INNER join sys.all_columns ac
           ON ac.object_id = t.object_id
           AND ac.column_id = sic.column_id
          WHERE sic.object_id = si.object_id
           AND sic.index_id = si.index_id
           AND sic.is_included_column = 0
          ORDER BY sic.key_ordinal, sic.index_column_id
          FOR XML PATH('')
          ), 1, 1, '')
         ))
  , Includes  = LTRIM(RTRIM(
        STUFF(
         ( SELECT ',' + ac.name
          FROM sys.index_columns sic
          INNER join sys.all_columns ac
           ON ac.object_id = t.object_id
          AND ac.column_id = sic.column_id
          WHERE sic.object_id = si.object_id
          AND sic.index_id = si.index_id
          AND sic.is_included_column = 1
          ORDER BY sic.key_ordinal, sic.index_column_id
          FOR XML PATH('')
         ), 1, 1, '')
        ))
  , si.filter_definition
FROM sys.schemas s
INNER JOIN sys.tables t on t.schema_id = s.schema_id
INNER JOIN sys.indexes si on si.object_id = t.object_id
WHERE t.[type] = 'U' -- USER_TABLE
AND s.name = 'dbo'
AND t.name IN ('Schedule' , 'ActivityType')
GROUP BY s.name, t.name, si.name, si.type_desc, si.is_primary_key, si.is_unique, si.is_disabled
   , t.object_id, si.object_id, si.index_id, si.filter_definition
ORDER BY s.name, t.name, si.is_primary_key DESC, si.name;


---

How to post data/code on a forum to get the best help.