How SQL PI Captures Execution Plans for Stored Procedures in SQL Server (4379315)

This article explains how SQL Performance Investigator (SQL PI) in Foglight captures execution plans for queries inside stored procedures and whether EXECUTE permissions are required to generate or view those plans.

How SQL PI Captures Plans from Stored Procedures

SQL PI captures execution details by monitoring runtime activity via SQL Server DMVs, Query Store (if enabled), and plan cache. When a stored procedure runs, SQL PI captures the individual queries it executes, including their associated execution plans.

• Plans are captured during runtime.

• SQL PI breaks stored procedures into individual query statements.

• Captured plans may be actual or compiled plans, depending on visibility and availability.

SQL PI does not require access to the stored procedure's source code.

Permission Requirements

To View Plans from Already-Executed Procedures

• SQL PI does not need EXECUTE permissions on the stored procedure.

• It does need:

  • VIEW SERVER STATE (for server-level DMVs)

  • VIEW DATABASE STATE (for database-level DMVs)

This enables SQL PI to read from:

• sys.dm_exec_query_stats

• sys.dm_exec_query_plan

• sys.dm_exec_sql_text

• Plan cache entries

To Generate an Execution Plan Directly

• If you attempt to generate a plan (estimated or actual) manually, you must have EXECUTE permission on the stored procedure.

  • Even estimated plans (using SHOWPLAN_XML) require SQL Server to parse and validate the procedure.

Clicking on "Generate an estimated execution plan" in SQL PI requests the estimated execution plan from SQL Server the same as pasting this text into SQL Server Management Studio and clicking on "Display Estimated execution plan"

Please refer to this external Microsoft Knowledgebase article titled "Display the Estimated Execution Plan" on how SQL Server displays the estimated execution plan and the permission requirements. 

Permission Summary