Multiple Plans in Cache
SQL Server will try 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 will 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 will first try 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 ...
Get Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
