To homepage To homepage
Solved

Intent of Recompile - Default/Pro/Con

T
Rank

On clean procedures generated by TX, they seem to have a RECOMPILE embedded into the procedure itself as a general rule. I think this is to keep them fresh to other changes in a project that get introduced. But do we know the exact reason why, and is there a way to remove other that doing a customized change table by table? 

icon

Best answer by rory.smith 9 May 2023, 17:14

View original
-Ted

2 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @ted.clark please see some of the reasons for recompiling a stored procedure here. Ultimately it is to optimize a procedure's query plan with regards to the current state of the database and its objects. It is not possible to “turn off” the recompile option out-of-the-box. Do you have a particular reason for wanting to remove recompile within the cleansing stored procedures (e.g. potential performance improvements)? 

rory.smith
Rank
Userlevel 6
Badge +7

Hi @ted.clark ,

I assume that the recompile itself is not causing measurable delay? If you take out the recompile and SQL Server retrieves an old plan from the store it could either lead to:

  • changes elsewhere not being taken into account, perhaps leading to a plan not picking up an index that has been added,
  • choices SQL Server would normally make, given the load generated by other processes running at that time, being skipped. If you are applying ExecutionTime as the Execution Management paradigm you may not always have exactly the same order of tasks running, and therefore potentially different query plans for the same syntax across separate executions. Scaling up/down can also affect query plans depending on the amount of headroom available

If SQL Server chooses unfortunate plans sometimes, this can usually be alleviated by using options like indexes / compression / different ETL. In a pinch you can force a specific query plan from the store, but that means taking (broad) responsibility for thinking about other changes’ effects on that plan.

Reply


Terms and ConditionsCookie settings