User complaining the query taking more than 50 sec to execute

rockerz2155 2017-01-10 15:17:08

rockerz2155 2017-01-11 15:38:32
Thanks for the response. I have attached the actual plan this time. Can you please recommend now.
Much text

Rob Farley 2017-01-11 02:02:30
Well, immediately I'm drawn to the Table Scan on Object11 that's feeding a Hash Match (Inner Join),and then later, a Table Scan on Object11 that's feeding a Hash Match (Aggregate). With an index on Column42 (which is being used for the Probe into the Hash Match), you'd be a lot better off. But your plan also has some Missing Index recommendations, which I'd suggest you consider.

 Object11(Column42, Column54) INCLUDE (Column17, Column53, Column52, Column51)
 Object11(Column54) INCLUDE (Column42, Column17, Column53, Column52, Column51)

The Scan that's feeding the Aggregate might be worth considering for a ColumnStore Index if the data is mostly insert-only, or having an indexed view to have the aggregates pre-calculated.

There are also a bunch of TVFs that I can't see the detail of. And without the statements, which have been shrunk right down to simply "Statement1" and "Statement3", I can't suggest rewrites. But sorting out Object11 is definitely going to be useful. Start with creating the first of those indexes to see if the impact is significant.

Rob Farley 2017-01-13 06:51:50
Oh! Now that I can see the Actual plan, I see you have TVFs that need work.

FN_NEXTGEN_PGM_CLAIM_LOSS_UDD_COMBINED looks like it's defined as a multistatement TVF but should be an inline TVF.

FN_NEXTGEN_CLAIM_LOSS_UDD_COMBINED does a bunch of looping, and shouldn't! I'd recommend you explore other options for doing that.

Oh, and FN_AEG_MVParam is bad. That should be an inline function if possible.

You could get some amount of improvement by having an index on POLICY_ANCHOR_DIM (POLICY_NBR, RECORD_TYPE_CD) INCLUDE (PREVIOUS_POLICY_NBR) – but you really should rework those functions a lot.