How to check Query Execution Plan of SQL Stored Procedure

To check the Query Execution Plan of an SQL Stored Procedure, you can use SQL Server Management Studio (SSMS) or other query profiling tools. Here’s how to do it using SSMS:

1. Open SQL Server Management Studio: Launch SQL Server Management Studio and connect to the SQL Server instance where your database and Stored Procedure are located.
2. Select the Database: From the Object Explorer, expand the Databases folder, and select the database that contains your Stored Procedure.
3. Enable Actual Execution Plan: Before executing the Stored Procedure, make sure the “Actual Execution Plan” option is enabled. You can find this option on the toolbar or go to the “Query” menu and check the “Include Actual Execution Plan” option.
4. Execute the Stored Procedure: Run the Stored Procedure by executing a SQL query that calls the Stored Procedure. You can do this by opening a new query window and writing a EXEC statement for the Stored Procedure. For example:
   EXEC YourSchema.YourStoredProcedureName @Param1 = 'Value1', @Param2 = 'Value2'; Replace YourSchema with the appropriate schema name and YourStoredProcedureName with the name of your Stored Procedure. Also, provide appropriate parameter values for @Param1 and @Param2.
5. View the Execution Plan: After executing the query, the “Execution Plan” tab should appear alongside the “Results” tab at the bottom of the query window. Click on the “Execution Plan” tab to view the graphical representation of the query execution plan. The execution plan shows how SQL Server is processing the query, including the operations performed, indexes used, and estimates of the cost of each operation. It can help you identify performance bottlenecks and areas for optimization.
6. Analyze the Execution Plan: Carefully review the execution plan to understand how the Stored Procedure is being executed. Look for any table scans, expensive operations, or missing indexes that might be impacting the performance.You can also hover over each operator in the execution plan to view additional details and statistics about that specific step.
7. Optional: Save the Execution Plan: If you want to save the execution plan for further analysis or to share it with others, you can do so by clicking on “File” in the SSMS menu and selecting “Save Execution Plan As…”. This will save the plan as an XML file.

Remember that the execution plan may vary based on the parameters passed to the Stored Procedure, so you might want to test the procedure with different parameter values to get a more comprehensive understanding of its performance.

AND finding the SQL stored procedure execution plan from last executions:

To find the SQL Stored Procedure execution plan from the last executions, you can use SQL Server’s dynamic management views (DMVs) or extended events. I’ll show you how to do it using DMVs, as they are easier to work with and provide valuable information about the execution plans. Specifically, we’ll use the sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs. Here’s the step-by-step process:

1. Open SQL Server Management Studio: Launch SQL Server Management Studio and connect to the SQL Server instance where your database and Stored Procedure are located.
2. Identify the Stored Procedure: First, you need to identify the name of the Stored Procedure you want to check the execution plan for.
3. Query the Dynamic Management Views: Use the following query to retrieve the execution plan of the last execution of the Stored Procedure:
   SELECT TOP 1
   qs.creation_time,
   qt.text AS [SQLText],
   qp.query_plan AS [ExecutionPlan]
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
   CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
   WHERE qt.objectid = OBJECT_ID(‘YourSchema.YourStoredProcedureName’)
   ORDER BY qs.creation_time DESC;
   Replace YourSchema with the appropriate schema name and YourStoredProcedureName with the name of your Stored Procedure.
4. Interpret the Results:
   creation_time: The timestamp of when the query plan was compiled.
   SQLText: The text of the Stored Procedure.
   ExecutionPlan: The XML representation of the execution plan.
5. View the Execution Plan: Copy the XML content from the ExecutionPlan column and paste it into a new query window. Then click on the “Query” menu and select “Display Estimated Execution Plan” or “Include Actual Execution Plan” (if it’s not already enabled).The graphical representation of the execution plan will be displayed, and you can analyze it to understand how the Stored Procedure is being executed.
6. Remember that the sys.dm_exec_query_stats DMV holds the query execution statistics for the entire SQL Server instance, and the above query will retrieve the execution plan for the last execution of the specified Stored Procedure. If you want to analyze execution plans for multiple executions or for a specific time range, you can modify the query accordingly, such as by using additional filtering conditions or joining with other DMVs.