For abstract plans to be captured for the SQL statements that can be optimized in stored procedures:
The procedures must be created while plan capture or plan association mode is enabled. (This saves the text of the procedure in sysprocedures.)
The procedure must be executed with plan capture mode enabled, and the procedure must be read from disk, not from the procedure cache.
This sequence of steps captures the query text and abstract plans for all statements in the procedure that can be optimized:
set plan dump dev_plans on go create procedure myproc as ... go exec myproc go
If the procedure is in cache, and the plans for the procedure are not being captured, execute the procedure with recompile. Similarly, once a stored procedure has been executed using an abstract query plan, the plan in the procedure cache is used so that query plan association does not take place unless the procedure is read from disk.
You can use set fmtonly on to capture plans for a stored procedure without actually executing the statements in a stored procedure.
