When a batch or remote procedure call (RPC) is submitted to SQL Server, the server checks for the validity and correctness of the query plan before it begins executing. If one of these checks fails, the batch may have to be compiled again to produce a different query plan. Such compilations are known as recompilations. These recompilations are generally necessary to ensure correctness and are often performed when the server determines that there could be a more optimal query plan due to changes in underlying data. this is a part of microsoft whitepaper. i just wanna know how does the sql data engine figures out that there could be a better plan that the stored one.
In general SQL Server will use the same execution plan forever unless there are changes. Changes to the query structure itself will lead to a new execution plan. Changes to the underlying structure, the addition of new indexes or changes to old ones will require a new execution plan. Changes to the statistics of the data, usually caused by data changes, but also manually initiated, can cause the creation of a new execution plan. If the plan is removed from cache due to age and pressure on the cache or because of manual extraction, a new execution plan will be created. If temporary tables are used in the query, then a new execution plan has to occur every time in order to allow for the creation of the temporary object. How and when SQL Server determines that it needs a new execution plan are well known. How it creates that execution plan.... that's magic.