SQLMentoring

Query 2:  Entity.IDX.Entity_EntityTypeDelFlag
   Actual rows returned:    169023
   Estimated rows returned: 132673

    EntityAssociation.IX_EntityAssociation_EntityID2
   Actual rows returned:    3371
   Estimated rows returned: 388

​Query 4:  Entity.IDX.Entity_EntityTypeDelFlag
   Actual rows returned:    169023
   Estimated rows returned: 132673
   
    EntityAssociation.IX_EntityAssociation_EntityID2
   Actual rows returned:    3383
   Estimated rows returned: 388

Where the major difference is the upper left join type and the noticeably larger number of rows from the right most lower query from the Entity table (Query 4).  However, this completely disagrees with the logical reads from the set statistics results.  Can someone point out why the large disagreement in query cost and cpu/io results.  I am puzzled with this result considering the index rebuilds.  .

and

Sometimes when I am comparing queries I get query plan costs that are very different, but the cpu and io numbers run just the opposite (in this case quite dissimilar).  Consider the two stored procedures below:

CREATE PROCEDURE [dbo].[Get_Communications_ByCompany]
@p_nCompanyID  int
AS
DECLARE @Error as int;
DECLARE @CompanyEntityID INT = (SELECT TOP 1 CompanyEntityID FROM dbo.UserCompanyGroup WHERE CompanyID = @p_nCompanyID);
SET NOCOUNT ON;
select         distinct c.CommunicationID, c.CommunicationTypeID, ct.TypeName,
               isnull(c.Name, '') as Name, c.CommunicationValue, c.DateAdded,
               c.AddedBy, c.DateUpdated, c.UpdatedBy
        FROM dbo.EntityAssociation ea (NOLOCK)
        JOIN dbo.Entity e2 (NOLOCK)
          ON e2.EntityID = ea.EntityID2
        JOIN dbo.Communication c (NOLOCK)
          on c.CommunicationID = e2.EntityIdentity
        JOIN dbo.CommunicationType ct (NOLOCK)
          ON ct.CommunicationTypeID = c.CommunicationTypeID
where   ea.EntityID1           = @CompanyEntityID AND
               ea.DelFlag                    = 'N' AND
               c.DelFlag                     = 'N' and
               e2.EntityTypeID               = 5 and
               e2.DelFlag             = 'N'
order by               c.CommunicationID     
SET @Error = @@Error;
RETURN @Error;
 

and

 

CREATE PROCEDURE [dbo].[Get_Communications_ByCompany2]
@p_nCompanyID  int
AS
DECLARE @Error as int;
DECLARE @CompanyEntityID INT = (SELECT TOP 1 CompanyEntityID FROM dbo.UserCompanyGroup WHERE CompanyID = @p_nCompanyID);
SET NOCOUNT ON;
select         distinct c.CommunicationID, c.CommunicationTypeID, ct.TypeName,
               isnull(c.Name, '') as Name, c.CommunicationValue, c.DateAdded,
               c.AddedBy, c.DateUpdated, c.UpdatedBy
        FROM dbo.EntityAssociation ea (NOLOCK)
        INNER HASH JOIN dbo.Entity e2 (NOLOCK)
          ON e2.EntityID = ea.EntityID2
        JOIN dbo.Communication c (NOLOCK)
          on c.CommunicationID = e2.EntityIdentity
        JOIN dbo.CommunicationType ct (NOLOCK)
          ON ct.CommunicationTypeID = c.CommunicationTypeID
where   ea.EntityID1           = @CompanyEntityID AND
               ea.DelFlag                    = 'N' AND
               c.DelFlag                     = 'N' and
               e2.EntityTypeID               = 5 and
               e2.DelFlag             = 'N'
order by               c.CommunicationID
SET @Error = @@Error;
RETURN @Error;
 
With the major different between the two queries being the INNER HASH JOIN on dbo.Entity.  I’ve rebuilt all the affected indexes and updated statistics (FULL) on dbo.EntityAssociation and dbo.Entity, but get same results. 

With statistics IO,Time on and running following code:

exec Get_Communications_ByCompany 1434
PRINT '******'
exec Get_Communications_ByCompany2 1434

 

 I get (partial results shown)
…
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, …
Table 'CommunicationType'. Scan count 0, logical reads 8, physical reads 0, …
Table 'Communication'. Scan count 0, logical reads 16, physical reads 0, …
Table 'Entity'. Scan count 0, logical reads 3520398, physical reads 0, …
Table 'EntityAssociation'. Scan count 1, logical reads 2558, physical reads 0, …
…
 SQL Server Execution Times:
   CPU time = 2672 ms,  elapsed time = 2784 ms.
******

...
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, …
Table 'CommunicationType'. Scan count 0, logical reads 8, physical reads 0, …
Table 'Communication'. Scan count 0, logical reads 16, physical reads 0, …
Table 'Workfile'. Scan count 16, logical reads 2752, physical reads 312, …
Table 'Entity'. Scan count 1, logical reads 789, physical reads 0, …
Table 'EntityAssociation'. Scan count 1, logical reads 2558, physical reads 0, r…
…
SQL Server Execution Times:
   CPU time = 531 ms,  elapsed time = 1223 ms.

With the following query plans respectively: