Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two almost identical queries running on the same SQL Server 2005 instance:

  1. The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application developer, just the DBA :).
  2. The second one is exactly the same as the first one, added a OPTION (RECOMPILE) at the end.

Nothing else has been changed.

The first one takes 55 seconds everytime its run.
The second one takes 2 seconds.

Both result sets are identical.

Why would this hint generate such a dramatic gain in performance?

The Books Online entry on RECOMPILE doesn't offer a much detailed explanation:

Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuses them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. For more information, see Recompiling Stored Procedures. RECOMPILE is also useful when you create plan guides. For more information, see Optimizing Queries in Deployed Applications by Using Plan Guides.

Since my query has a lot of local variables, my guess is that SQL Server is able to (seriously) optimize it when I use the OPTION (RECOMPILE) query hint.

Everywhere I look people are saying that OPTION (RECOMPILE) should be avoided. The explanation for this is generally that using this hint SQL Server is not able to reuse this exection plan and therefore has to waste time recompiling it everytime.
(But) Given the gigantic performance advantage, I'm inclined to think that using this query hint this time would be a good thing.

Should I use it? If not, is there a way I can force SQL Server to use a better execution plan without this hint and without altering the application?

share|improve this question
3  
The query uses local variables as you state or parameters? Variables don't get their values "sniffed" without RECOMPILE so that could explain it but just to be clear. Also if parameters are involved is the LINQ one using a previously compiled plan or have you ensured that is "freshly" compiled when doing these comparisons? –  Martin Smith Mar 19 '12 at 18:08
    
@MartinSmith its using local variables. I ensured that the execution plan was reused when not using the RECOMPILE and the parameters were the same. The execution plan was of objtype Adhoc. When I changed the parameters, another execution plan was compiled and cached. With the RECOMPILE hint, a new plan was generated everytime and it wasn't even cached afterwards, just discarded. –  ivanmp Mar 19 '12 at 18:24
    
Reason for asking is because I haven't seen a LINQ generated query that uses variables. AFAIK they usually use sp_executesql and parameters. Can you add the query to your question? –  Martin Smith Mar 19 '12 at 18:25
    
@MartinSmith gist.github.com/30988fdda7e0a84086e8 –  ivanmp Mar 19 '12 at 18:32
3  
@MartinSmith just curious why you wouldn't make that an answer? It's absolutely dead on. Anyone trying to answer this will be forced to copy your "answer" here in the comments. –  Grant Fritchey Mar 19 '12 at 19:05
show 4 more comments

1 Answer

up vote 6 down vote accepted

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables

When the optimiser has no usable statistics at all for a column it will guess that an = predicate will match 10% of rows, BETWEEN 9%, and any of >, >=, < and <= will match 30%. If there are column statistics available an = predicate will be treated differently as below.

Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name. So, for example, if the column column_name contains all unique values, then a selectivity estimate of 1/(number of unique values in column) will be used, which is accurate.

So this is essentially the same as using for OPTIMIZE FOR (UNKNOWN). It may well be more accurate than a flat 10% guess but it isn't tailored to the specific values that you are querying.

To force SQL Server to optimize a query every time it is run, and use the values of local variables to estimate cardinality and cost during optimization of the query, add the RECOMPILE query hint to the query.

With the use of RECOMPILE you presumably are getting more accurate cardinality estimates and therefore a different plan with join orders/ join types more suited to the number of rows returned from different parts of your actual query.

share|improve this answer
    
Thank you, sir! –  ivanmp Mar 20 '12 at 12:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.