Menu
I am able to view the Estimated Execution Plan (Management Studio 9.0) for a query without a problem but when it comes to stored procedures I do not see an easy way to do this without copying the code from the ALTER screen and pasting it into a query window, otherwise it will show the plan for the ALTER and not the procedure. Even after doing this, any inputs are missing and I would need to DECLARE them as such.
Is there an easier way to do this on stored procedures?
Edit: I just thought of something that might work but I am not sure.
Could I do the estimated execution plan on
exec myStoredProc 234 
902
If so, how do I display the execution plan for a completed SQL query? Assuming that the SQL is still in the library cache, you can use the SQL statement to extract the "address" column from v$sqlarea and then use the address to display the execution plan from v$sql_plan.
Once an execution plan is created, it is stored in the plan cache which is part of memory and by reusing an existing plan it can reduce the execution time and improve performance. Each SQL Server execution plan has an age factor, the older an execution plan is it may not still be valid due to changes in the data.
Following is the procedure to view the actual execution plan. Step 1 Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name. Step 2 − Click New Query option seen on the above screen and write the following query.
SET SHOWPLAN_ALL ON GO -- FMTONLY will not exec stored proc SET FMTONLY ON GO exec yourproc GO SET FMTONLY OFF GO SET SHOWPLAN_ALL OFF GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
