Reporting Query taking long time
I have attached the actual execution plan of the query.
[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:
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.