Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: How to avoid precompile

    Hi Gurus,

    I found that whenever my procedure is called, it is precompiled. Is there any way I can tell SQL Server not to precompile a particular stored procedure ?

    I appreciate your responses.

    thanx..
    -Bheemsen

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Is that really what you mean?
    You can use the with recompile clause to force it to be recompiled on every run.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Re: How to avoid precompile

    Q1 Is there any way I can tell SQL Server not to precompile a particular stored procedure ?
    By "precompiled" do you mean that the procedure has been compiled, and the plan is being retrieved and reused (normally a desirable thing), without a re-compile, and there is a need to prevent such caching and plan reuse???

    If so, then:

    A1 Yes. Consider adding a with recompile statement to the procedure; that should cause it to be re-compiled each time it is executed.

    If not, please elaborate.

  4. #4
    Join Date
    May 2002
    Posts
    62
    My question was NOT to recompile.

    Thanx anyway for the response.

    -Bheemsen

  5. #5
    Join Date
    Oct 2002
    Posts
    369
    RE:
    My question was NOT to recompile. Thanx anyway for the response. -Bheemsen

    Q2 Is there any way I can tell SQL Server NOT to recompile a particular stored procedure ?
    A2 Indirectly, there may be e.g.(if there is a with recomple statement, remove it).

    Some kinds of things that cause recompilation include data changes in rows, schema changes and certain set commands; seperating such things out of existing stored procedures may or may not improve performance by reducing recompiling. For example: If a procedure includes ddl statements; one might consider creating several seperate procedures and calling the "ddl procedures" from other non-ddl stored procedures as required (test alternate designs for performance differences prior to implementation).

    Bear in mind that while recompiling may be resource intensive, (in which case reusing a high cost rated plan is generally desirable), not all tsql recompilation incurs high cost rated plans i.e.(it may not always be "cost effective" to keep a smaller infrequently used plan in the Procedure Cache). Sql Server algorithyms effectively weight Procedure Cache space, recompile costs, and recent plan usage statistics.

    That said, you may wish to consider running Profiler traces (listing recompile events and statements) and then analyze the column detailing the reason for recompilation. Doing so should both give a reasonable idea of why procedures are being recompiled, as well as some ideas about strategies that may improve performance.

  6. #6
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    An SP will be compiled at first run.
    It will then be recompiled if there are any changes in the objects on which it depends.
    This can be if there are changes to the table structures, indexes, views, ... or if it depends on temp tables that are not created within the SP.

    Changes in statistics can also cause a plan to be marked as invalid.

  7. #7
    Join Date
    Jan 2003
    Posts
    1
    Note also that if you have temporary tables (not the new table variable) or User defined Functions (UDFs), SQL may choose to recompile the SP every time. SQL also recompiles when the statistics are out-of-date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •