I have a DELETE
statement running against a table with a full-text indexed column, a handful of foreign keys with cascade
enabled. It looks like this:
DELETE FROM dbo.STUDENTS WHERE STUDENTID=@STUDENTID
Occasionally a plan is compiled which includes very high row estimates for all index operations, such that the DELETE
takes a very long time and causes locking.
I have attempted to force a good plan in QueryStore but this doesn't actually work, showing last forced plan failure description
of NO_PLAN
.
I have ensured that there are no schema changes that could be invalidating the plan.
Looking at the execution plan, I see that the DELETE
involves a join to a system table holding the FT index:
Does this join to the FT index mean that plan-forcing is not supported?
Best Answer
When looking at the plan-forcing limitations one of the bullet points is:
However, this limitation does not mean that a delete should fail the plan forcing.
Consider these 4 queries, two
SELECT
statements and twoDELETE
statements on a table with a fulltext index on theval
column:The only query that is failing is the 2nd one, which is using the
CONTAINS
statement:With the failure reason being
DQ_NO_FORCING_SUPPORTED
.The plans for the two delete statements, which also show the same
clustered index merge
operators get forced:This source gives more information on
DQ_NO_FORCING_SUPPORTED
:To answer the question
My answer would be that this is not the reason as you should see the
DQ_NO_FORCING_SUPPORTED
instead of theNO_PLAN
error. The reason could be due to another limitation rendering the plan invalid, index changes, added foreign keys, ....Extra tests
When adding a table and creating a foreign key with on delete cascade that references the table used in the queries above.We would expect the delete to fail since we cannot use the forced plan any longer.
As expected, we get the![enter image description here](https://i.stack.imgur.com/ZZ2fc.png)
NO_PLAN
on the two delete statementsRe-forcing the plans gives us salvation:
And removes the
NO_PLAN
issueThe same is true for a table with a fulltext index that has a foreign key (with on delete cascade) that references the main table where the delete query is running on.
Regarding the high / low estimates
If the delete is not executed that much, adding
OPTION(RECOMPILE)
could help with the high row estimates as this can give you a better estimation due to the optimizer 'seeing' the variable at runtime.Posting a different question with the query plan might bring up a different workaround / solution.