How do I get the execution plans for a Stored Procedure?
A:
If you're using MS Query Analyizer then there's a button you can click up on the tool bar. If I remember correctly (MS QA not installed on this PC) then it looks like a speech bubble with some green or blue chart marks on it?
Sonny Boy
2009-10-15 20:02:57
+1
A:
In SQL Server 2005, just type up the EXEC YourProc statement and hit Ctrl-L.
An alternative is to "edit" the stored proc by commenting out the SP definition parts, defining the parameters as local variables, and Displaying the Estimated Execution Plan (Ctrl-L).
Austin Salonen
2009-10-15 20:11:22
+2
A:
From SQL Server Management Studio check the 'Include Actual Execution Plan' option in the toolbar, then execute the procedure, see Displaying Graphical Execution Plans (SQL Server Management Studio).
or Run SET SHOWPLAN_XML ON; then execute the procedure on the same connection, see Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL).
or capture the Showplan XML Event Class in the Profiler see Displaying Execution Plans by Using SQL Server Profiler Event Classes.
Remus Rusanu
2009-10-15 20:17:13
+1 Interesting, for anyone else looking for the Showplan XML Event, it can be found in the performance section
Andomar
2009-10-15 20:28:54
@Andomar: Profiler event plans are very usefull because is the only way to get the plan of a query run by an application *as it runs*. Capturing the SQL and then running from SSMS sometimes produces different plans.
Remus Rusanu
2009-10-15 20:34:48
@Remus Rusanu: Exactly, we've seen that happen and it's really hard to troubleshoot. We were able to resolve it with query hints like "loop join" and "with (index=)". The event class will be a useful tool when it happens next!
Andomar
2009-10-15 20:46:55