Query Plans

Last Post 15 Mar 2011 05:15 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
New Member
New Member

15 Mar 2011 03:59 PM
I am having trouble getting a clear answer to a question?

How many query plans for a given stored procedure be in cache at one time?

I have been told two: a parallel query plan and a non-parallel query plan.

But what about there are many users calling that same stored procedure and there is tons of memory available in the cache?
New Member
New Member

15 Mar 2011 05:15 PM
Each user will get what is called an execution context which is built from the query plan and fills in the blanks for things like user, SET Settings, Parameters etc. If you have 5 users executing a sp at the exact same time there will be 5 execution contexts but only 1 query plan (not counting paralle vs single). Execution contexts can be reused for the next user that calls the sp. But if after a period of time lets say only 2 users call the sp at once it will remove the other 3 execution contexts. But it can always generate more as needed and remove them when not used. But these are not separate query plans, there is only the one.

Acceptable Use Policy