-2

We have query which is inserting data to heap:

INSERT INTO [heap]
(
    80 COLUMNS...
)
SELECT
   14 columns
   65 NULL VALUES   
   , 'vvvvvvjvvvvvmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmvv'
FROM [heap]  h_
JOIN [dbo].[PartitionedTableWith2BilionsOfRecords] bas_
  ON (bas_.[ROWID] = h_.[ROWID])
WHERE h_.[hvr_op] IN (@0)
      AND bas_.[rowid] > @1
      AND bas_.[rowid] < @2;

*(anonymized) In heap we do not have any indexes. Table from join is partitioned and has around 2 bilions of records. Also we have nonclusteterd index on rowid.

In database Force Parametrization is enabled.

Unfortunately this query in most executions is building not optimal plan:

enter image description here

Was trying to force optimal plan (20670):

enter image description here

Unfortunate I am getting forced plan failure - NO_PLAN. According to information from other website added extended event: enter image description here

Which is showing forced plan failures and have only below information:

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.

Question:

  1. Why sometimes force of this plan is working, sometimes not ?
  2. Is plan guide will be better option for this case ?
  3. What can I do to force plan for every execution of query ?

1 Answer 1

4
  1. Not every plan SQL Server can generate is capable of being forced.

    See Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan.

  2. Maybe. Worth trying.

  3. Hard to speculate from the information provided.

If you want a more specific answer, provide a minimal reproduction of the issue in the question and state the version of SQL Server you are using. It is understandable to anonymize data, but that might not prevent you demonstrating the problem with a small, manufactured data set.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.