SQL Performance Investigator (PI) Repository
SQL Performance Investigator (PI) in Foglight enables you to monitor and analyze the performance of selected database platforms in your environment. PI provides in-depth analysis capabilities including session details, execution plans, change tracking, blocking analysis, and historical performance comparison.
This section covers the following key areas:
- SQL Performance Investigator (PI) Repository
Supported Database Platforms
PI is supported for the following database platforms:
- SQL Server
- Oracle
- Azure SQL Database
- SQL Server Analysis Services (SSAS)
- MySQL
- PostgreSQL
To use PI, a dedicated database repository must be installed to store the collected data.
PI Repository Database Requirements
The PI Repository database must be hosted on Microsoft SQL Server. This is the only supported database platform for the PI Repository itself, regardless of which database platforms you are monitoring with PI.
SQL Server Version and Edition
| Edition | Supported Versions |
|---|---|
| Standard or Enterprise | Microsoft SQL Server 2016, 2017, 2019, 2022, and 2025. Supported on RDS and on Azure SQL Managed Instance |
Additional Notes:
- Microsoft SQL Server 2016 for Windows - Requires SP1 or SP2 for SQL Server 2016 Standard edition.
- Amazon RDS for SQL Server 2016 - Requires SP1 or SP2 for SQL Server 2016 Standard edition.
Support to install PI repository on SQL Server Always On Availability Group. For more information, see KB326158.
While PI can monitor various database platforms (Oracle, MySQL, PostgreSQL, etc.), the PI Repository that stores the collected performance data must always be a SQL Server database.
SQL Server Express is not supported due to the 10GB database size limit.
PI Repository Architecture
The PI Repository is a dedicated database that stores performance data collected by the Foglight agents. This repository enables:
- Historical analysis of database performance
- Execution plan tracking and comparison
- Change tracking for configuration and schema changes
- Blocking and lock analysis
- Top SQL statement analysis
Data Retention and Time Pyramid
The PI Repository uses a time-based data aggregation model (time pyramid) to efficiently store and retrieve performance data at different granularities:
| Granularity | Retention Period |
|---|---|
| 1 minute | 6 hours |
| 15 minutes | 3 days |
| 1 hour | 2 weeks |
| 6 hours | 30 days |
| 1 day | 90 days |
| 1 week | 2 years |
Default 1-Minute Table Behavior
By default, PI 1-minute tables are historically preserved for approximately 1 day only. Specifically:
- Each table contains 6 hours of data
- At any given point in time, there are 4 such tables of every type in the PI Repository
- The 1-minute data is accessible only for approximately 6 hours. Queries for data older than 6 hours will use 15-minute granularity data instead.
Query Resolution
When viewing PI data:
- Timeframes of 1 hour or less: If 1-minute tables are available for the selected timeframe, they will be used for improved resolution
- Timeframes greater than 1 hour: Falls back to aggregated data tables
- Graph Display: When 1-minute tables are available for the time period, PI graphs show points for each minute for improved resolution
Installing the PI Repository
To enable PI from the Databases Dashboard:
Click Enable PI under the Monitoring Agent column for the desired instance.
On the Administration Dashboard, complete these two steps to enable PI:
Step 1: Configure the Agent Manager
Verify that the monitoring Agent Manager is attached to a SQL PI repository. Select the Agent Manager on which the SQL PI repository manager should be installed, then click Apply.
- If a SQL PI repository already exists on the selected Agent Manager, no further action is required for the configuration.
- If no SQL PI repository exists on the selected Agent Manager, the SQL PI Repository Settings dialog appears.
Step 2: Configure Repository Settings
In the SQL PI Repository Settings dialog:
- Authentication: Select the authentication method for connecting to the PI Repository database
- Database Name: Enter the name for the PI repository database
- Raw Data: Specify the directory path for raw data storage
To use gMSA (Group Managed Service Account) authentication for connections to the PI Repository, select Local User in the Authentication drop-down. This requires that your Foglight repository and Agent Manager are configured to run as a Windows Service logged on with the gMSA account. The gMSA account requires access to the SQL instance.
- If the given SQL PI repository user does not have sufficient privileges, the Insufficient Privileges dialog will be displayed. Enter an admin user and password, then click Grant Privileges.
- Select the checkbox of the required agent to configure SQL PI settings. Click Enable. In the SQL PI Repository dialog box, select the required Agent Manager host and click Apply.
The SQL PI is now enabled for the selected instance.
Administering SQL Performance Investigator
The SQL Performance Investigator view in the Administration dashboard allows you to:
- Enable and disable SQL PI monitoring for selected agents
- Start and stop the collection of change tracking data
- View the status of PI-enabled instances
To access the SQL PI administration:
- Navigate to the database-specific Administration dashboard
- Select the SQL Performance Investigator category
- Select one or more agents to enable, disable, or modify change tracking status
