1

We have a weekly stored procedure that ocassionaly picks up wrong execution plan that causes much longer execution times.

I've tried to force correct execution plan but still every few weeks the execution plan forcing fails with following error:

Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

Sometimes even after such error the good execution plan is created and used (see following screenshot) 3 months plan history

The good execution plan: https://www.brentozar.com/pastetheplan/?id=B1NIXXohp

Slow execution plan: https://www.brentozar.com/pastetheplan/?id=Sy3fmmjnp

Query itself is visible in the plans (tried to insert it here but it gets strange formating).

When I implemented the index suggested by the second plan:

Cannot insert duplicate key row in object 'MEX.Agreement_Transaction_Table' with unique index 'IX_AgrTblIncludes'. The duplicate key value is (202304, 1, 0, 0xa0be430067091800).

What could be the reason of picking wrong plan/plan forcing failures?

Improve this question
0

2 Answers 2

Reset to default
1

When I implemented the index suggested by the second plan:

Cannot insert duplicate key row in object 'MEX.Agreement_Transaction_Table' with unique index 'IX_AgrTblIncludes'. The duplicate key value is (202304, 1, 0, 0xa0be430067091800

First of all it is NOT the index suggested in the second plan, SQL Server just cannot know if your columns gives a unique combination so any suggested index is NOT unique, and what you've created IS unique. The suggested index was

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [MEX].[Agreement_Transaction_Table] ([Accounting_Week_Num],[Committed_Bln],[Deleted_Flag]) INCLUDE ([FT_L_Agreement_Id],[FT_L_Accounting_Week_Id],[FT_L_Agreement_Transaction_Id],[FT_L_Agreement_Transaction_Type_Id],[Agreement_Transaction_Id],[Agreement_Transaction_Db_Id],[Agency_Id],[Agreement_Db_Id],[Agreement_Transaction_Type_Code],[Agreement_Id],[Company_Code],[Creation_Date],[Product_Version_Db_Id],[Product_Version_Id],[Value],[Write_Off_Reason_Code],[Effective_Accounting_Week_Num],[Transaction_Date],[Committed_Date])

and it has 3 key columns and it is not unique, the index giving you the error is unique and has at least 4 columns (the values are truncated in the error output)

The reason why these two plans are different is the parameter value used for compilation, for earlier date (202313) you've got better plan as there is statistics for this value, and for 202333 it estimates just one row that is wrong.

Your main problem here is the wrong structure of Agreement_Transaction_Table, it's a partitioned heap that seems to have last partition limit value 202052, so it's not maintained anymore, that is why the estimations are wrong, you should add missing partitions to correct it.

In my opinion it should be clustered index on Accounting_Week_Num, but I don't know the reasons why do you choose to have a heap

Improve this answer
0
0

Have you checked when stats are updated and tried updating them prior to the insert query?

I notice you are filtering on "week_num", you could be experiencing the ascending key problem.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.