Skip to main content
2 of 2
edited tags
Sean Gallardy
  • 35.6k
  • 3
  • 46
  • 86

Execution plan showing high hash match

i have a sql query running from database 1 that joins table company on to a view

SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode]
FROM [dbo].[view] AS [K]  

JOIN [database2].[dbo].[Company] AS [C]  
ON [K].[cmpCompanyCode] = [C].[CmpCompanyCode];

this is my view

SELECT RTRIM([CM].[fkOrganizationIDParent]) AS [fkOrganizationIDParent],
          [C].[cmpCoID],
          RTRIM([CE].[ClientNumber]) AS [cmpCompanyCode],

          ISNULL([CE].[z_DoNotReplicate], 0) AS [DoNotReplicateSupervisors],
          ISNULL([CE].[z_StopPTOReplication], 0) AS [StopPtoReplication]        
    FROM   [dbo].[Client] AS [CM] WITH(NOLOCK)
          JOIN [dbo].[Entity] AS [CE] WITH(NOLOCK) ON [CM].[fkProjectID] = [CE].[fkProjectID]
          JOIN [database2].[dbo].[Company] AS [C] ON [CE].[ClientNumber] = [C].[CmpCompanyCode]
    WHERE  [CM].[z_K] = 1
          AND ISNULL([CE].[z_Exclude], 0) = 0   
          AND [CM].[crStatus] <> 'Terminated'
          AND [CM].[IsActive] = 1
          AND [CE].[IsActive] = 1
          AND ISNULL(RTRIM([CE].[ClientNumber]), '') <> '';

but when i run the first query my execution plan shows me a high hash.

Output List says dbo.company.CmpCoid, expr1016 hash

i have indexes on the dbo.company table as follows

CREATE NONCLUSTERED INDEX [IDX_CompanyCode] ON [dbo].[Company]
(
    [CmpCompanyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [CDX_CompanyName] ON [dbo].[Company]
(
    [CmpCompanyName] ASC,
    [CmpCoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

im not sure how to fix this?