Running SQL query with options(recompile) always faster

Question

this is a possible duplicate of this question, though I think my example is a bit more in depth and I would appreciate more definite answers. I'm trying to understand why running the simple query with option(recompile) performs better.

DECLARE @p9 nvarchar(4000)
SET @p9=N'Alex%'

SELECT ContactId as CandidateId
FROM Candidate
            WHERE
                (
                    @p9 IS NULL
                    OR
                    Forename LIKE @p9
                    OR 
                    PreferredName LIKE @p9
                    OR
                    Surname LIKE @p9
                )

When running without option(recompile) (no matter how many times I run it)

(1166 row(s) affected)
Table 'Candidate'. Scan count 5, logical reads 9762, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The very same query but option(recompile) added in the end

(1166 row(s) affected)
Table 'Candidate'. Scan count 3, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If I do NOT use variables in the query, but constants instead, then all is fine. I presume it is to do with parameter sniffing, but the question is WHY? Also what would be the drawbacks of leaving option(recompile) for production code?

I also done some diagnostic tests running the query from within the application, and is seems with option(recompile) it is always faster results.

Update: running exec sp_updatestats has no effect and select with option(recompile) still performs times better.

Thank you for your time.

Answer 1

Option(recompile) generates a new plan based on the current statistics, at the expense of compiling a new plan. If your statistics are up to date then 99 times out of 100, you will get an optimal plan (rather than a cached query plan which might not be appropriate for the particular set of parameters you passed in, compared to when the plan was cached. This is termed parameter sniffing)

"Also what would be the drawbacks of leaving option(recompile) for production code?"

That's fine for a reporting query, but would be a bad idea for a query running many times per second, since the cost of recompiling the plan each time could likely outweigh the cost of actually executing the query!

Also, be aware that Using Option(recompile) has the drawback that the procedure won't show up in the relevant DMV's.