How to prevent recompile for ad hoc query-CodePudding

When I first run the following query, a recompile occurs taking (7s). If I run it again results come back in <100ms. However when I only change the numeric value, it insists on doing another recompile each time.

Is there a way to prevent recompile without resorting to wrapping it with an SP (which was already validated to work)?

set statistics time on;

declare
    @o bigint = 3374707

select * from ComplexTableValuedFunction(@o)
option (keep plan, use hint ('DISABLE_PARAMETER_SNIFFING')) -- has no effect in ad hoc?

set statistics time off;

Side issue: The statistics messages only lists the short execution time (~100ms) and never the compile time for some reason. SQL Sentry verifies the compile time is ~7s.

CodePudding user response：

Any time an ad-hoc batch changes, even by a single character, it is recompiled.

To prevent a recompile, you need to pass the batch through using sp_executesql, and properly parameterize it. At this point, you will get parameter sniffing, unless you add the hint 'DISABLE_PARAMETER_SNIFFING'

set statistics time on;

EXEC sp_executesql
  N'
select * from ComplexTableValuedFunction(@o);
',
  N'@o bigint',
  @o = 3374707;

set statistics time off;

The actual EXEC statement does not have a query plan, so changing the value will not affect anything