Rule of thumb on when to use WITH RECOMPILE option

I understand that the WITH RECOMPILE option forces the optimizer to rebuild the query plan for stored procs but when would you want that to happen?

What are some rules of thumb on when to use the WITH RECOMPILE option and when not to?

  • is it required to pass timestamp value in entitymodel to insert a data into sql table
  • Table Schema With No Circular Reference With Same Table
  • Order selection
  • how to use coalesce with in, in the where part
  • Full text search does not work if stop word is included even though stop word list is empty
  • The schema update is terminating because data loss might occur
  • What’s the effective overhead associated with just putting it on every sproc?

    5 Solutions collect form web for “Rule of thumb on when to use WITH RECOMPILE option”

    As others have said, you don’t want to simply include WITH RECOMPILE in every stored proc as a matter of habit. By doing so, you’d be eliminating one of the primary benefits of stored procedures: the fact that it saves the query plan.

    Why is that potentially a big deal? Computing a query plan is a lot more intensive than compiling regular procedural code. Because the syntax of a SQL statement only specifies what you want, and not (generally) how to get it, that allows the database a wide degree of flexibility when creating the physical plan (that is, the step-by-step instructions to actually gather and modify data). There are lots of “tricks” the database query pre-processor can do and choices it can make – what order to join the tables, which indexes to use, whether to apply WHERE clauses before or after joins, etc.

    For a simple SELECT statement, it might not make a difference, but for any non-trivial query, the database is going to spend some serious time (measured in milliseconds, as opposed to the usual microseconds) to come up with an optimal plan. For really complex queries, it can’t even guarantee an optimal plan, it has to just use heuristics to come up with a pretty good plan. So by forcing it to recompile every time, you’re telling it that it has to go through that process over and over again, even if the plan it got before was perfectly good.

    Depending on the vendor, there should be automatic triggers for recompiling query plans – for example, if the statistics on a table change significantly (like, the histogram of values in a certain column starts out evenly distributed by over time becomes highly skewed), then the DB should notice that and recompile the plan. But generally speaking, the implementers of a database are going to be smarter about that on the whole than you are.

    As with anything performance related, don’t take shots in the dark; figure out where the bottlenecks are that are costing 90% of your performance, and solve them first.

    Putting it on every stored procedure is NOT a good idea, because compiling a query plan is a relatively expensive operation and you will not see any benefit from the query plans being cached and re-used.

    The case of a dynamic where clause built up inside a stored procedure can be handled using sp_executesql to execute the TSQL rather than adding WITH RECOMPILE to the stored procedure.

    Another solution (SQL Server 2005 onwards) is to use hint with specific parameters using the OPTIMIZE FOR hint. This works well if the values in the rows are static.

    SQL Server 2008 has introduced a little known feature called “OPTIMIZE FOR UNKNOWN“:

    This hint directs the query optimizer
    to use the standard algorithms it has
    always used if no parameters values
    had been passed to the query at all.
    In this case the optimizer will look
    at all available statistical data to
    reach a determination of what the
    values of the local variables used to
    generate the queryplan should be,
    instead of looking at the specific
    parameter values that were passed to
    the query by the application.

    The most common use is when you might have a dynamic WHERE clause in a procedure…you wouldn’t want that particular query plan to get compiled and saved for subsequent executions because it very well might not be the exact same clause the next time the procedure is called.

    generally a much better alternative to WITH RECOMPILE is OPTION(RECOMPILE)
    as you can see on the explanation below, taken from the answer of this question here

    When a parameter-sensitivity problem is encountered, a common piece of
    advice on forums and Q&A sites is to “use recompile” (assuming the
    other tuning options presented earlier are unsuitable). Unfortunately,
    that advice is often misinterpreted to mean adding WITH RECOMPILE
    option to the stored procedure.

    Using WITH RECOMPILE effectively returns us to SQL Server 2000
    behaviour, where the entire stored procedure is recompiled on every
    execution. A better alternative, on SQL Server 2005 and later, is to
    use the OPTION (RECOMPILE) query hint on just the statement that
    suffers from the parameter-sniffing problem. This query hint results
    in a recompilation of the problematic statement only; execution plans
    for other statements within the stored procedure are cached and reused
    as normal.

    Using WITH RECOMPILE also means the compiled plan for the stored
    procedure is not cached. As a result, no performance information is
    maintained in DMVs such as sys.dm_exec_query_stats. Using the query
    hint instead means that a compiled plan can be cached, and performance
    information is available in the DMVs (though it is limited to the most
    recent execution, for the affected statement only).

    For instances running at least SQL Server 2008 build 2746 (Service
    Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another
    significant advantage over WITH RECOMPILE: only OPTION (RECOMPILE)
    enables the Parameter Embedding Optimization.

    It should only be used when testing with reprentative data and context demonstrate that doing without produces invalid query plans (whatever the possible reasons might be). Don’t assume beforehand (without testing) that an SP won’t optimize properly.

    Sole exception for manual invocation only (i.e. don’t code it into the SP): When you know that you’ve substantially altered the character of the target tables. e.g. TRUNCATE, bulk loads, etc.

    It’s yet another opportunity for premature optimization.

    Note: I have plenty of points. If a newby submits the same answer below, and you agree, upvote theirs.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.