Can't help the engine to choose the correct execution plan

Question

The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.

Environment:

• SQL Server 2016
• standard edition

Objects:

• wide table with the following columns:

  ID BIGINT PK IDENTITY
  Filter01 
  Filter02 
  Filter03 
  .. and many columns   
  

• stored procedure returning visible ID from the given table depending on filter parameters

• the table has the following indexes:

  PK on ID
  NCI on Filter01 INCLUDE(Filter02, Filter03)
  NCI on Filter02 INCLUDE(Filter01, Filter03)
  

Basically, in the routine I am creating three temporary tables - each holding current filtering values and then join them with the main table. In some cases, Filter02 values are not specified (so the join with this table is skipped) - the other tables are always joined. So, I have something like this:

SELECT *
FROM maintable 
INNER JOIN #Filter01Values -- always exists
INNER JOIN #Filter02Values -- sometimes skipped
INNER JOIN #Filter03Values -- always exists

So, how the IDs are distributed - in 99% of the cases it will be best to filter by Filter02Value and I guess, because of this, the engine is using the NCI on Filter02 INCLUDE(Filter01, Filter03) index.

The issue is that in the rest 1% the query fails badly:

In green is the Filter02 values table and you can see that filtering on this does not reduce the read rows at all. Then when the filtering by Filter01 is done (in red) about 100 rows are returned.

So, this is happening only when the stored procedure is executed. If I execute its code with these parameters I nice execution plan:

In such case, the engine is filtering by Filter01 first and Filter02 third.

I am building and executing dynamic T-SQL statement and I add OPTION(RECOMPILE) at at the end, but it does not change anything. If I add WITH RECOMPILE on the stored procedure level, everything is fine.

Note, the values in the temporary tables for filtering are not populating in the dynamic-tsql statement. The tables are defined, populated and then the statement is built.

So, my questions are:

• is the engine building a new plan for my dynamic statement as I have OPTION(recompile) - if yes, why is wrong
• is the engine using the values populated in my filter02 temporary table to build the initial plan - maybe yes, that's why it is choosing the wrong plan
• using recompile on procedure level feels very hard/lazy fix - do you have any ideas how I can assist the engine further and skip this option - new indexes for examples (I have try a lot)

Answer 1

From your plans I see the problem in this statement:

INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID]) 
select SI.[SurveyInstanceID] 
from [dbo].[SurveyInstances] SI 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF 
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID] 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ClientAccessStatusIDs CAS 
        on SI.[ClientAccessStatusID] = CAS.[ClientAccessStatusID] 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF 
        on SI.[CampaignID] = CF.[CampaignID] 
where SI.[IsOkForExport] = 1 AND SI.[HoldExport] = 0 AND ( PSF.[AllowedBy] = 'C' )
option(RECOMPILE)

There is no need in recompile here since there is no parameters at all, you have only constants here. The difference in two plans is in the JOIN ORDER:

The correct join order is first

[dbo].[SurveyInstances] SI 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF 
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]

and then everything else, this join applies all the filters and produces only 104 rows that go to final resultset.

Your "slow" plan shows different join order:

[dbo].[SurveyInstances] SI 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF 
        on SI.[CampaignID] = CF.[CampaignID] 

that produces 17 mln of rows.

You don't want this join order so your choices are to use FORCE ORDER hint or to simply save the result of the correct join to another temp table and then to join it with the rest of your query:

select SI.[SurveyInstanceID] 
into #si_psf
from [dbo].[SurveyInstances] SI 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF 
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID] 
where SI.[IsOkForExport] = 1 AND SI.[HoldExport] = 0 AND ( PSF.[AllowedBy] = 'C' );


INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID]) 
select SI.[SurveyInstanceID] 
from #si_psf SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ClientAccessStatusIDs CAS 
        on SI.[ClientAccessStatusID] = CAS.[ClientAccessStatusID] 
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF 
        on SI.[CampaignID] = CF.[CampaignID]; 

I suggest you to use the second method.