Slow running query - help needed

New to performance tuning. Maybe someone could give a little help. thanks.

KAMExecutionplan.pesession (7.5 kB)
avatar image By NewToThis1 1 asked Feb 03, 2014 at 03:45 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Feb 03, 2014 at 03:52 PM

I see two warnings in the plan. One is about an implicit convert (I assume in the join CRF1.Sys_Product = CAT1.Record_key) - what are the data types of these two columns, and why don't they match? I believe this is the cause for the expensive Sort operator down at the bottom of the graphical plan. The other is that a missing index was suggested (on FACT_SYS_RE_CRF_GL([Partition]) with a bunch of INCLUDE columns). Have you tried dealing with either of these issues?

10|10000 characters needed characters left
▼ Viewable by all users
0
2 answers: sort voted first ▼

link textlink textHi Aaron, Many thanks for your help, much appreciated!

The data types are as follows:-

• CRF1.Sys_Product is NvarChar (10)

• CAT1.Record_key was NvarChar(250) now changed to NvarChar(10)

I found some CRF1.Sys_Product records with ‘blank’ values and I have now updated these with the appropriate CRF1.Sys_Product values.

I have added the missing index and it runs slightly quicker. Is there any more that can be done? link text

kamexecutionplan_1.pesession (7.1 kB)
kamexecutionplan_2.pesession (6.4 kB)
avatar image By NewToThis1 1 answered Feb 03, 2014 at 04:50 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
▼ Viewable by all users
0

Hi NewToThis

The new plan still has a warning: PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,[CRF1].[Sys_Product],0)" so this would be worth looking at to begin with.

I'm wondering if the index seek on [Insight_DBU_11202].[dbo].[FACT_SYS_RE_CRF_GL].[IDX_KAMTUNE01].(NonClustered) could be tweaked to assist the subsequent sort operation, I'm wondering if adding the Surrogate_Customer_PK as a key value instead of a non-key value (include) would assist or remove the sort?

See how that goes for now :)

Mart

avatar image By Mart 125 answered Feb 04, 2014 at 01:59 PM
more ▼
(comments are locked)
avatar image NewToThis1 Feb 05, 2014 at 12:50 PM

Thanks Mart. I’ve resolved the “Convert_Implicit” issue; it’s funny how sometimes you can’t see the obvious when it’s staring you in the face! I was joining on two fields that had different data types.

I have made the change that you suggested to the index and it’s helped!.

I’ve also added a few more indexes and now the expensive operations are Hash Match, Merge Join and an Index Seek. Is there anything else that could be looked at?

avatar image Mart Feb 05, 2014 at 01:03 PM

Hi NewToThis1

Thant's good news, especially about the index! Can you post up the new plan so we can see what's left?

Mart

avatar image NewToThis1 Feb 05, 2014 at 01:07 PM +

kamexecutionplan_2.pesession added to my previous reply. (I couldn't get it to add here. Thanks again!

10|10000 characters needed characters left
▼ Viewable by all users
0
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.