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 be found, it will look for an object called Orders in the dbo schema. If user dan executes the exact same query, the object can be resolved in a completely different way (to a table in the default schema of the user dan), so sue and dan could not share the plan generated for this query. Because there is a possible ambiguity when using the unqualified object name, the query processor will not assume that an existing plan can be reused. However, the situation is different if sue issues this command:
SELECT * FROM dbo.Orders
Now there’s no ambiguity. Anyone executing this exact query will always reference the same object. In the sys.syscacheobjects view, the column uid indicates the user ID for the connection in which the plan was generated. For adhoc queries, only another connection with the same user ID value can use the same plan. The one exception is if the user ID value is recorded as -2 in syscacheobjects, which indicates that the query submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method.
Tip It is strongly recommended that objects are always qualified with their containing schema name, so that you never need to rely on implicit resolutions, and the reuse of plan cache can be more effective.
© Microsoft. All Rights Reserved.