October 26, 2016 at 5:23 pm
Hey guys,
I have a very strange performance issue in our production environment and I'm a bit mystified as to what's causing it. A stored procedure was running without issue until all of a sudden it started running terrible.
I have isolated the issue to a few UPDATE statements but the strange thing is the issue doesn't appear in UAT, even though everything is identical in terms of the database. The only thing I can see is a drastic difference in the estimated execution plans. Again, tables etc involved in the statement are identical. I've attached the estimated plan from both UAT and PROD and included the actual execution plan from UAT (In PROD it just takes forever).
I'm wondering if someone can give me some clues as to why they might be so different?
I'm aware the query itself isn't great and needs to be tuned (this is being done as part of a bigger project) but I would just like to know what's happening.
Thanks in advance,
Chris
October 26, 2016 at 5:37 pm
I think I may have discovered the problem.
Someone seems to have created an additional index that looks to have been throwing the query out.
October 26, 2016 at 9:25 pm
Writing queries like this is asking for trouble.
Must be created by some automation tool and is extremely difficult for optimizer.
It might be not the index you found, people normally do not create indexes when there are no performance issues.
There must be differences in data statistics which make optimizer think there will be 88k selected in Prod, when there are no rows selected in Test.
Try to re-write the query this way, may be it will help:
SELECT
ck_rate_table_key= h.tabid
FROM EDW_SSA_EBX.dbo.utb_ebx_hrat__lnkh
WHERE EXISTS (
SELECT *
FROM EDW_SSA_EBX.dbo.uvw_ebx_risk r
INNER JOIN EDW_SSA_EBX.dbo.utb_ebx_risk__tag rt
ON rt.ck_policy_id = r.policy_id AND rt.ck_id = r.id AND rt.ck_type = r.type AND rt.ck_seq = r.seq ANDrt.ck_seqn = r.seqn
WHERE r.[type] = h.[type]
AND rt.ck_product = 'EBX-' + h.[product_cd]
ANDrt.ck_rate_table_key = 0
AND(r.[sex]= h.[sex] OR h.[sex] = '' )
AND(r.[smok]= h.[smok] OR h.[smok] = '' )
AND(r.[bene_perd]= h.[bene_perd] OR h.[bene_perd] = 0 )
AND(r.[waiting_days]= h.[waiting_days] OR h.[waiting_days] = 0 )
AND(r.[bene_indx]= h.[bene_indx] OR h.[bene_indx] = '' )
AND(r.ck_composite_key= h.ck_composite_key OR h.ck_composite_key = '' )
AND(r.[buyback_option]= h.[buyback_option] OR h.[buyback_option] = '' )
AND(r.[aal_ind]= h.[aal_ind] OR h.[aal_ind] = '' )
AND(r.[ins_formula]= h.[ins_formula] OR h.[ins_formula] = '' )
AND(r.[occu_grp_cd]= h.[occu_grp_cd] OR h.[occu_grp_cd] = '' )
ANDr.[si] BETWEEN h.[min_si] AND h.[max_si]
ANDr.[fixed_rate_period] = h.[fixed_rate_period]
ANDr.[effd] BETWEEN h.[effd] AND h.[endd]
)
_____________
Code for TallyGenerator
October 26, 2016 at 9:29 pm
Thanks for you reply.
I discovered someone had added an index on the risk__tag table which was causing the strange plan in production. I agree the code isn't great and I'm currently working my way through a mountain of inefficient SQL.
October 27, 2016 at 5:14 pm
Chris-475469 (10/26/2016)
Thanks for you reply.I discovered someone had added an index on the risk__tag table which was causing the strange plan in production. I agree the code isn't great and I'm currently working my way through a mountain of inefficient SQL.
There is more than just a mountain of inefficient SQL code here. Your data requiring concatenation isn't good. Nor is those "match on a data hit OR some default value". I would also probably OPTION (RECOMPILE) everyone of those queries for a variety of reasons.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2016 at 5:29 pm
Yeah I'm aware of those things as well. Tuning this system is going to be a long and painful process.
One thing I am surprised about is how bad the plan in production was with the extra index that was added. Compared to UAT anyway.
Can the addition of an index really cause SQL to produce that bad a plan? Considering it wasn't seeking on that index as far as I could see.
October 28, 2016 at 4:02 pm
Chris-475469 (10/27/2016)
Yeah I'm aware of those things as well. Tuning this system is going to be a long and painful process.One thing I am surprised about is how bad the plan in production was with the extra index that was added. Compared to UAT anyway.
Can the addition of an index really cause SQL to produce that bad a plan? Considering it wasn't seeking on that index as far as I could see.
I'm not convinced you got a "bad plan" with the index. But regardless, there are umpteen things that can result in the optimizer producing a plan that is suboptimal for a given set of inputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2016 at 4:46 pm
Can the addition of an index really cause SQL to produce that bad a plan? Considering it wasn't seeking on that index as far as I could see.
Yes, it can, at least as I vaguely remember and understand one potential issue in SQL Server optimization. The problem comes about because SQL initially "thinks" it can use that nonclustered index. Then later on in the optimization process, SQL decides it doesn't want to use that index after all, at which point it reverts to a table scan.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply