Microsoft® SQL Server® 2008 Internals

Multiple Plans in Cache

SQL Server tries to limit the number of plans for a query or a procedure. Because plans are reentrant, this is easy to accomplish. You should be aware of some situations that cause multiple query plans for the same procedure to be saved in cache. The most likely situation is a difference in certain SET options, as discussed previously.

One other connection issue can affect whether a plan can be reused. If an owner name must be resolved implicitly, a plan cannot be reused. For example, suppose user sue issues the following SELECT statement:

SELECT * FROM Orders;

SQL Server first tries to resolve the object by looking for an object called Orders in the default schema for the user sue, and if no such object can be found, it looks ...