Option (recompile) speeds up query execution

Question

Posting this as a broader question, but please let me know if actual query would help figuring this out.

We have a query that takes ~30 sec to run for a number of reasons judging by execution plan. Query is generated by Entity Framework and of course has some inefficiencies so that's not the point.

When we DBCC FREEPROCCACHE and the submit the query, original ~30 sec run time. When query is hinted with OPTION (RECOMPILE) it is instant (and the plan is much more reasonable).

Why would the result differ if the query hits the server for the first time in first scenario and therefore should compile the plan for the first time too?

Side notes:

Indexes were rebuilt before through https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Statistics were updated through sp_updatestats
Other hints were tried to achieve the same thing - trace flag 4136 etc
Before going to "recompilation", we explored params sniffing and tried OPTIMIZE FOR UNKNOWN

Update - added plans:

This is one of the footprints of a parameter-sniffing problem. — RBarryYoung, Jun 17 '16 at 19:43
Could be benefitting from the [parameter embedding optimisation](http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options) Without the query and plans who knows? — Martin Smith, Jun 17 '16 at 19:46
If you compare the "good" plan with recompile and the "bad" plan, you should figure out what's the difference. The parameters used to compile it are in the leftmost node. — James Z, Jun 17 '16 at 19:46

Martin Smith · Accepted Answer · 2016-06-17T21:33:17.473

This doesn't look like parameter sniffing.

The CompiledValue and RunTimeValue is the same for all parameters even in the bad.sqlplan

<ParameterList>
  <ColumnReference Column="@p__linq__16" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />
  <ColumnReference Column="@p__linq__15" ParameterCompiledValue="N'ABCD4'" ParameterRuntimeValue="N'ABCD4'" />
  <ColumnReference Column="@p__linq__14" ParameterCompiledValue="(10776)" ParameterRuntimeValue="(10776)" />
  <ColumnReference Column="@p__linq__13" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />
  <ColumnReference Column="@p__linq__12" ParameterCompiledValue="(0)" ParameterRuntimeValue="(0)" />
  <ColumnReference Column="@p__linq__11" ParameterCompiledValue="N'ABCD4'" ParameterRuntimeValue="N'ABCD4'" />
  <ColumnReference Column="@p__linq__10" ParameterCompiledValue="N'ABCD4'" ParameterRuntimeValue="N'ABCD4'" />
  <ColumnReference Column="@p__linq__9" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
  <ColumnReference Column="@p__linq__8" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
  <ColumnReference Column="@p__linq__7" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
  <ColumnReference Column="@p__linq__6" ParameterCompiledValue="N'ABCD4'" ParameterRuntimeValue="N'ABCD4'" />
  <ColumnReference Column="@p__linq__5" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />
  <ColumnReference Column="@p__linq__4" ParameterCompiledValue="(513)" ParameterRuntimeValue="(513)" />
  <ColumnReference Column="@p__linq__3" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />
  <ColumnReference Column="@p__linq__2" ParameterCompiledValue="(513)" ParameterRuntimeValue="(513)" />
  <ColumnReference Column="@p__linq__1" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />
  <ColumnReference Column="@p__linq__0" ParameterCompiledValue="(513)" ParameterRuntimeValue="(513)" />
</ParameterList>

Rather it looks as though you are benefitting from The Parameter Embedding Optimization.

The query text is cut off in the plan but I can see places where you are comparing parameters with literals. Perhaps this is a catch all query

When you use OPTION (RECOMPILE) the plan compiled only has to work for the passed parameter values. SQL Server can look at the value of the parameters passed and effectively replace the highlighted expressions with TRUE or FALSE.

This potentially allows it to simplify out whole branches of the plan if they are not relevant to the parameter values being passed.

A simple example of this would be

EXEC sys.sp_executesql
  N'SELECT * FROM master..spt_values WHERE @Param <> 0 OPTION (RECOMPILE)',
  N'@Param INT',
  @Param = 0;

The plan is compiled when @Param=0 and it only has to be correct for this value so the @Param <> 0 can be evaluated at compile time as false and the plan doesn't access the table at all.

Without the OPTION (RECOMPILE) you see this

EXEC sys.sp_executesql
  N'SELECT * FROM master..spt_values WHERE @Param <> 0',
  N'@Param INT',
  @Param = 0;

Even though the sniffed parameter value and the runtime parameter value are the same the plan cannot be optimised to the same extent as it will be cached and still need to work if passed a different parameter value.

Option (recompile) speeds up query execution

1 Answers1

Linked