How to create a custom view with SQL PI data? (4232319)

PI extension is enabled and a customized view of PI data is needed.

How to create such a view?

PI collects a lot of workload and performance data about the activity run within an instance. This information is saved in the PI embedded repository which is separate from the Foglight repository. As a result, the PI data is only presented in the PI dashboard and is not available for custom dashboards and reports as other information collected by the Foglight cartridge is.

To address this problem, several custom views have been created. They allow building custom dashboards and reports to provide more flexibility in consuming performance information.

The aim of this document is to describe the views available and provide a step-by-step instruction on how to use them.

Note: Information presented is relevant for both Foglight for SQL Server and Foglight for Oracle, unless otherwise indicated.

• The views takes the instance name as a parameter.
• A view can be made into report and scheduled to run periodically

The following views are available:

1. Top SQL Statements - Show the top SQL statements by active time in a certain instance during selected time frame. Allow the statements to be filtered by Database, User, Program or combination of all
2. Top Batch - SQL Server only. Show the top Batchesby active time in a certain instance during selected time frame. Allow the batches to be filtered by Database, User, Program or combination of all
3. Wait Events - Show the top wait events occurred in an instance for selected timeframe

Create a custom dashboard based on PI views

1. Create a new dashboard and select “Use All Data”
   
   
   
2. Press "Next" and give the custom dashboard a "Name". Also select an "Auto Refresh Rate"
   
   
   
3. Press "Next" and select "Single-Column Layout"
   
   
   
4. Press "Finish" and then "Cancel" on the next pop up screen
   
5. Go to the "Data" tab and expand “Databases | SQL Server | Instances”
   
6. Find “Instances” and drag the required instance to the “Parameterized Inputs” on the upper level of the screen
   
   
   
7. Go to the "General" tab and press “Add View”
   
   
   
8. Choose “Select a View Template” and press “Next”
   
   
   
9. Expand the “Databases” tree and select the required view: "SQL Performance Investigator - Top Batches" or "SQL Performance Investigator - Top Statements".
   
10. Press “Finish”
    
    
    
11. Dashboard is finished. It is possible to change the instance selected on the right pane
    
    
    
12. A view for "Wait Event Table" is also available under "SQL Performance", but the instance name cannot be a parameter - the instance selected when adding the view, will be the one used
    
    

Create report based on PI views

1. To create a report based on the view, it is possible to click on the icon on the right hand side of the table and select "Export":
   
   
   
2. In the pop-up press "Next"
   
   
   
3. Press "Save as Report" to create a report
   
4. To schedule the report, select "Run" in the Exported report dashboard created:
   
   
   
5. Follow the parameters requested to run the report for interval suited for you and to schedule it properly. for example: if you wish the report to be generated each hour, select "Last Hour" for the report "Time Range" parameter, and then schedule it "Hourly" on the Schedule screen.
   
6. Once the report is created, you can manage and see all reports created under the "Manage Report" dashboard