Viewing execution plans in SQL PI for SQL Server (4230302)

To begin,

1. Log into the Foglight Console and navigate to the Globalview databases dashboard 

   

2. Select a database agent with the SQL PI extension configured
3. In the SQL PI performance tree, select SQL Statements

   

4. In the bottom half Top SQL Statements chart, an Analyze Plan button is present at the top of the table. This can be clicked to select the SQL statement to drill into.

   

5. In the Advanced Analytics | Change Tracking area, the user can click on a row that displays an execution plan change.

   

6. Filter out the other change tracking categories and then click on one of the execution plan changes.

   

7. The execution plan dashboard displays the following content
   • Top Bar
   • Date — Displays all the execution date and time of the selected statement\batch
   • Type — Displays the type of the execution plan.
   • Plan handle — Displays the SQL handle on the execution plan.
8. In the Plan analysis section, the execution plan is displayed in tabs which present the cost of each operation and object of each step of the plan.

   

9. By pressing the Generate Plan button the execution plan is generated and can be viewed by clicking on Compare Plan.

   

10. This opens a pop up with a comparison of all available execution plans of the selected statement.
11. Clicking the Open in SSMS button will download a .sqlplan file which can be associated and opened with SQL Server Management Studio

The following video details how-to view execution plans in SQL PI.