Reporting Query taking long time

Arvind 2015-09-04 01:10:29

The following query taking more than 7 hours to complete. This is a reporting query . I have made sure stats are updated for all the tables involved. Can you please give your inputs to reduce the execution time?

I have attached the actual execution plan of the query.

SQLkiwi 2015-09-04 06:33:20
In this particular case, almost all the work performed is ultimately pointless because no rows match the predicates on the ClaimFolder table:

[Dim].[ClaimFolder].[EndDate]>'2015-08-21' 
AND [Dim].[ClaimFolder].[StartDate]<='2015-08-21' 
AND [Dim].[ClaimFolder].[SourceSystemID]<>(9) 
AND [Dim].[ClaimFolder].[IronshoreSettlementAmount] IS NOT NULL 
AND CONVERT(date,CONVERT(datetime,[Dim].[ClaimFolder].[IronshoreSettlementDateID],0),0)>='2000-01-01' 
AND CONVERT(date,CONVERT(datetime,[Dim].[ClaimFolder].[IronshoreSettlementDateID],0),0)<='2000-01-01' 
AND CONVERT(date,CONVERT(datetime,[Dim].[ClaimFolder].[ClaimFolderOpenDateID],0),0)>='2000-01-01' 
AND CONVERT(date,CONVERT(datetime,[Dim].[ClaimFolder].[ClaimFolderOpenDateID],0),0)<='2000-01-01' 
AND 
    CASE 
        WHEN [Dim].[ClaimFolder].[IronshoreSettlementDateID]=(0) 
        OR [Dim].[ClaimFolder].[IronshoreSettlementDateID]=(2958463) 
        THEN NULL 
        ELSE CONVERT(date,CONVERT(datetime,
            [Dim].[ClaimFolder].[IronshoreSettlementDateID],0),0) 
    END IS NOT NULL

I have highlighted this operation in the plan fragment below:

Plan Fragment

Checking this condition before the query starts, or using temporary tables to break up the logic of the query, would likely help in general.

As I've said before, you need to patch this instance from SP2 to SP3, and longer term look at moving to using columnstore technology (ideally in SQL Server 2016).

Ironically, most of the slow performance in this case is down to the way row-mode parallel execution is implemented. Notice all the work below the merge join that is ultimately never needed (since the hash join above it has zero rows in its hash table). SQL Server lacks the infrastructure in this case to tell those threads to stop work.

So, if this exact query were run at MAXDOP 1, I would expect it to complete very quickly. Of course, serial execution would not be appropriate for executions that actually need to do a lot of work, so that's not a suggestion.

Arvind 2015-09-04 07:53:51
Thanks Paul for the quick response. I will follow the recommendations.