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?
Solved
Intent of Recompile - Default/Pro/Con
Best answer by rory.smith
Hi
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.
+5Hi
+7Hi
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
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
