0

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:

enter image description here

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:

enter image description here

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)
4
  • 3
    Instead of images, upload the actual plan xml to Paste The Plan and add the links to your question.
    – Dan Guzman
    Jan 16, 2020 at 12:34
  • @DanGuzman This link does not work very well. I have never managed to make it work and always get - The supplied XML is not a valid SQL Server query plan xml. I will share the plan in other way.
    – gotqn
    Jan 16, 2020 at 14:09
  • drive.google.com/drive/folders/… - take them from here - it's easy to get the slow query using plan explorer.
    – gotqn
    Jan 16, 2020 at 14:14
  • Try to first execute select SI.[SurveyInstanceID] 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' ) and save this result into #tmp. The difference in your plans is in the join order in the INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances statement. Enforce the correct order by saving the result of the join above into temporary table
    – sepupic
    Jan 16, 2020 at 15:50

1 Answer 1

0

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:

enter image description here

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.

2
  • Yes, but I can rely that filtering by #ProtoSurveysFiltered will be always better and in many cases filtering by the #CampaignsFiltered is faster. I just want the engine to decide when to do each join. After all, I am giving to it indexes, statistics and rely that the better plan is going to be use.
    – gotqn
    Jan 16, 2020 at 18:23
  • And the most disappointing thing is that when I script the code, the engine is using the better plan, so it can do it :D I just want to help him to do it each time.
    – gotqn
    Jan 16, 2020 at 18:27

Your Answer

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

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