When looking at the plan-forcing limitations one of the bullet points is:
Distributed query or full-text operations
However, this limitation does not mean that a delete should fail the plan forcing.
Consider these 4 queries, two
SELECT statements and two
DELETE statements on a table with a fulltext index on the
WHERE CONTAINS(val, N'WOW');
WHERE id <500 and id > -1;
The only query that is failing is the 2nd one, which is using the
With the failure reason being
The plans for the two delete statements, which also show the same
clustered index merge operators get forced:
This source gives more information on
Cannot execute query because plan conflicts with use of distributed
query or full-text operations
To answer the question
Does this join to the FT index mean that plan-forcing is not
My answer would be that this is not the reason as you should see the
DQ_NO_FORCING_SUPPORTED instead of the
The reason could be due to another limitation rendering the plan invalid, index changes, added foreign keys, ....
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.
CREATE TABLE dbo.fulltexreference(id int identity(1,1) primary key not null, t1id int, val varchar(max));
ALTER TABLE dbo.fulltexreference add constraint FK_test foreign key(t1id) REFERENCES dbo.fulltextindexed(id) ON DELETE CASCADE;
As expected, we get the
NO_PLAN on the two delete statements
Re-forcing the plans gives us salvation:
And removes the
The 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.