TheSQLGuru (4/30/2015)

---

Hmm. Wouldn't adding a meaningful index to a table cause "affected" plans to recompile? Bit tired at the moment so could be confuzzled ... :w00t:

That would be so nice if it were true. That would save all the headache folks have with this issue.

Here is, as I suspect, why that can't happen. Lets say that you had a table that had no index at all and there were several thousand rows (it could happen). Next you build a stored procedure that selects one or more rows from the table based upon a parameter. The plan that is cached at that time uses a table scan as there is no alternative.

Now you add and index that well supports the WHERE clause in your procedure. Unfortunately SQL Server does not try to guess which procedures could be improved by using the index. If you recompile the procedure then SQL Server looks at indexes that exist at that moment and caches a new plan based on that.

In some cases SQL Server could guess a bit as there are internal tables that indicate which objects are used by a procedure. Still one could construct a SQL statement in a procedure and have that executed in the procedure and that would not show in that case. Further a procedure can, and often does, call other stored procedures.