Why does OPTION RECOMPILE cause a predicate pushdown

azure-sql-databaseexecution-planquery-performancerecompilesql server

I have a SQL query that is made out of a nested mess of views and table-valued functions going on for at least 4 levels deep (I didn't have the time or patience to go through it all, it's hundreds of lines of code at each level).

I've been trying to understand why is it that when I run the base query with OPTION (RECOMPILE) it works very fast, however when I run it without this OPTION, it runs very slow.

I've made sure to clear the plan cache before this happens and even when generating a new plan, it's suboptimal, however, the OPTION (RECOMPILE) one is fast.

I've inspected both plans and noticed that for the plan with OPTION (RECOMPILE) the parameters that are passed.

SELECT [p].[Activity]
    ,[p].[ActivityType]
    ,[p].[Company]
    ,[p].[Flags]
    ,[p].[Id]
    ,[p].[Name]
    ,[p].[Priority]
    ,[p].[Filters]
    ,[p].[Priority]
    ,[p].[Classification]
    ,[p].[Number]
    ,[p].[TaskFilter]
    ,[p].[TaskType]
    ,[p].[User]
FROM (
    SELECT *
    FROM [ActivProdStatuses]('ProdJobTask', 0)
    ) AS [p]
WHERE (
        (   ([p].[User] = 'some_value') AND (([p].[Flags] & 8) = 0) ) 
           AND ([p].[Activity] = 'unique_value') 
        )
    AND  
      (CASE 
        WHEN ([p].[Flags] & 4) <> 0
         THEN CAST(1 AS BIT)
         ELSE CAST(0 AS BIT)
       END = 1 )
ORDER BY [p].[Priority]
OPTION (RECOMPILE)

In the plan without OPTION RECOMPILE, I have a part in my plan where the bulk of the time is spent moving around useless data which is later filtered by the FILTER operator (you can see 0B coming out of the FILTER).

enter image description here

The FILTER operator has all the filtering parameters that come with the procedure (unique_value and some_value) and some other filtering parameters that are determined in the nested levels. The nested levels contain themselves other TVF's with parameters determined from OUTER APPLY'ed queries.

enter image description here

In the OPTION RECOMPILE version, those parameters are pushed down to the execution plan (I'm guessing it's called Predicate Pushdown) and filtered directly in the first step, when data is read from disk.

From what I looked at, this is my conclusion for why this is happening and why the OPTION RECOMPILE plan is more efficient. You can see the same data access for the same table, below and the part of the more efficient OPTION RECOMPILE plan.

enter image description here

Now, my question is why would the OPTION RECOMPILE plan behave differently than when just generating a new plan for a new query, with the same parameters / values passed to it. What does the OPTION RECOMPILE do?

I've tried to search online and see if it does "force" a predicate pushdown but I couldn't find anything specific about this.

You can find the anonymized plan for the slow execution here. Also, the anonymized plan for the OPTION RECOMPILE execution is here.

I tried running the query once with OPTION (RECOMPILE) and then removing the hint and immediately running the query again (same parameters both times). The first run is fast, the second is slow.

I did think to generate the "fast" plan in the hopes that it would be stored in the cache and get reused when the OPTION RECOMPILE is not specified anymore. However, I believe the query plan hashes differ and the cached plan will not be reused since there are more changes than just the values passed in to the filtering clauses.

Best Answer

Now, my question is why would the OPTION RECOMPILE plan behave differently than when just generating a new plan for a new query, with the same parameters / values passed to it. What does the OPTION RECOMPILE do?

The main thing OPTION (RECOMPILE) does is to compile a plan for the current values of any parameters, rather than reusing any cached plan. The newly-generated plan is not cached for reuse.

The second thing it enables is the Parameter Embedding Optimization. SQL Server replaces any parameters with their literal values before optimization.

This might sound trivial, but can enable important simplifications. For example, any type conversions or complex expressions the value is part of can be evaluated early (constant folding). Note the CONVERT_IMPLICIT in your screenshot to convert the supplied varchar value to nvarchar. I note your database has forced parameterization enabled.

Your plans are large and anonymized, but I would suggest parameter embedding and the resulting major simplifications it can enable are responsible for the dramatic performance improvement.

When you run the same query without OPTION (RECOMPILE) the same simplifications are not possible because SQL Server cannot safely embed the parameter values, since the plan might be reused for different values.

More information and background in my article (linked above) Parameter Sniffing, Embedding, and the RECOMPILE Options. It contains a worked example showing step-by-step how parameter embedding and constant folding can work to improve an execution plan.

Related Question