July 22, 2010 at 2:27 pm
When we execute a stored procedure, the execution plan is stored in cache. The maximum number of versions of the execution plan on a server is two (from SQL Server tutorial).
My question is how we know which execution plan is used in the next execution. How do we display all versions of the execution plans?
Many thanks for any input in advance.
July 22, 2010 at 3:54 pm
The following will pull every plan from the plan cache:
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO
Reference from BOL:
http://msdn.microsoft.com/en-us/library/ms189747.aspx
This might be a starting point for what you are after.
July 22, 2010 at 4:01 pm
SQL ORACLE (7/22/2010)
The maximum number of versions of the execution plan on a server is two (from SQL Server tutorial).
Not versions. There's one parallel and one non-parallel plan stored in cache. It's not current version and older version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply