Execution plan showing high hash match

1 vote

0 answers

42 views

sql-server sql-server-2008-r2 index-tuning view hashing

                          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**




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? 
                        

Asked by deanpillow (11 rep)

Feb 15, 2024, 04:26 PM
Last activity: May 3, 2024, 11:25 AM

Execution plan showing high hash match

Related Questions