Two major scenarios of SQL Server execution plan recompilation

1. Re-compiling related to correctness

1. To add columns to a table or view, delete the columns.

2. Add constraints, defaults, rules, delete constraints, default values, and rules for the table.

3. Add an index to the table or view.

4. If you plan to use no index, the index is deleted.

5. Delete statistics from the table.

6, increase the trigger, delete the trigger.

2. Optimization-related recompilation

1, manually updated the statistics information.

2, statistical information is too old.

Two major scenarios of SQL Server execution plan recompilation