How do I obtain a Query Execution Plan in SQL Server

Question

How can I get a query execution plan for a query / stored procedure In Microsoft SQL Server?

Answer 1

There are several ways to get an execution plan; which strategy you pick will depend on your situation. Normally, you can get a plan using SQL Server Management Studio, but if for some reason you can't run your query in that tool, you might find it useful to be able to retrieve a plan using SQL Server Profiler or by looking through the plan cache.

Using SQL Server Management Studio.
The "Include Actual Execution Plan" menu item, which can be located under the "Query" menu, must be checked before running your query as usual in order to capture an execution plan in SQL Server.

You should run the stored procedure as follows if you're attempting to acquire the execution plan for statements in the method:

exec p_Example 42

When your query has finished running, a new tab labeled "Execution plan" ought to show up in the results window. If you run a lot of statements, this tab can show a lot of plans.
From here, you may open SQL Server Management Studio and view the execution plan, or you can right-click the plan and choose "Save Execution Plan As..." to save it to a file in XML format.

See also:

• Execution Plan Basics
• SHOWPLAN Permission and Transact-SQL Batches
• SQL Server 2008 – Using Query Hashes and Query Plan Hashes
• Analyzing the SQL Server Plan Cache

If you need to know more about SQL, it is recommended to go for the SQL Online Course today.