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).
The FILTER operator has all the filtering parameters that come with the procedure (
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.
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.
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.
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.