Slow running query – help needed

NewToThis1 2014-02-03 15:45:39

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

Aaron Bertrand 2014-02-03 15:52:39
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?
NewToThis1 2014-02-03 16:50:57
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

Mart 2014-02-04 13:59:54
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 🙂


NewToThis1 2014-02-05 12:50:59
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?

Mart 2014-02-05 13:03:04
Hi NewToThis1

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


NewToThis1 2014-02-05 13:07:21
kamexecutionplan_2.pesession added to my previous reply. (I couldn't get it to add here. Thanks again!
Mart 2014-02-05 13:07:56
Cool, will take a look.
Mart 2014-02-06 09:43:49
Hi Again NewToThis1

Have you had a go at applying some filters to the indexes? As you're using constants in your query you could add one or two filtered indexes (the filters have to be simple so you may need two but have a play) to see if that can satisfy the query. This could potentially be a big hit on data movements

With regard to the joins being a high percentage, the entire plan has to be 100% so there will always be costs. Whether or not these joins are worth looking into futher is down to the new execution time of the query – what was it to satrt with an what is it now…what's the percentage improvement so far?

On a word of caution, when popping indexes in to aid a query remember they have a cost and the overall workload should be consisdered. Take a look at the index, how many queries are using it along with the type of work load on the box ect. to ensure the the benefit is worth the cost.

Someone else may be able to help with greater theory behind the joins and if they should be delvied into or if the optimizer is doing the right thing.

Cheers for now


Mart 2014-02-14 14:50:00
Hi NewToThis1, sorry I haven't got back sooner I've been a little busy. Do you still need the final plan looking at or has it reached a acceptable performance?