Costly HASH MATCH Aggregate

Question

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], [incidentDetails].[IncidentStatus_FieldValue], [incidentDetails].[IncidentStatus_Description], Report.Id ReportId, Form.Id FormId
FROM
[dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
ON detailsPage.Id = supplement.PageId
INNER JOIN
(
    SELECT ReportId, Max(FormNumber) RecentFormNumber FROM
    (
        SELECT Report.Id ReportId, FormId, COALESCE(SupplementNumber, '0000') formNumber
        FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
        INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
        INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id 
        INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId 
        LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId) FormNumbers
        GROUP By ReportId) RecentForm
ON Report.Id = RecentForm.ReportId AND
RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

I analyzed the plan in Sentry Plan explorer and learned that the HASH JOIN Aggregate is the costly operation in the plan. I am trying to avoid/remove that HASH JOIN someway.? If any of you have experienced similar situation please give some suggestion.

Additional Info:

The plan says Hash key is built on Table Report and column Id. In-fact the Idcolumn is the primary key in the Report table ,so a clustered index is present also a Non-clustered index is created for Id column. Still why Hashing is required for Report.Id?

Here is the screenshot of the HASH MATCH Aggregate operation.

Info: When i select TOP 100 or 1000 it doesn't show HASH MATCH (Aggregate).

Thanks

Answer 1

This is not a full answer, but I need more room than a comment allows, so...

First, you should consolidate your query - you have an unnecessary step in it:

INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id 
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId 
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm

Second, you should get rid of the redundant use of the Report table, ending up with a query like this:

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], 
    [incidentDetails].[IncidentStatus_FieldValue], 
    [incidentDetails].[IncidentStatus_Description], 
    Report.Id ReportId, 
    Form.Id FormId
FROM [dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
    ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
    ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
    ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
    ON detailsPage.Id = supplement.PageId
INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id 
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId 
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm
    ON Form.ReportId = RecentForm.ReportId 
    AND RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

With these simplifications, please check the query plan again.