Foglight for SQL Server 5.7.5.41 - PI Schema Reference Guide

SQL PI Repository Schema

Welcome to the Foglight for SQL Server SQL PI Schema Reference Guide.

This guide provides conceptual information and instructions on how to query the SQL PI repository.

For more information, see the following topics:

About the SQL PI repository

SQL PI allows you see enterprise-level performance at a glance. You can determine the root-cause of performance deviations and identify changes to your SQL Server environment quickly and easily. The repository allows you to save historical data for up to three years, and allows you to see the changes made over the time.

Connecting to the SQL PI repository

SQL PI repository is based on a PostgreSQL® database (http://www.postgresql.org). Any Postgres database administration and development utility, such as pgAdmin (http://www.pgadmin.org), is sufficient to connect to the repository. In order to connect to the repository, you need the following information:

Your agent repository configuration can be found under the menu Administration > Agents > Agent Status > (agent_name)> Edit Properties. In the list of properties, search for the SQL PI properties:

The default user and password is postgres.

Understanding Time Ranges and Data Intervals

The time range in SQL PI history mode is built on a pyramidal model of granularity, such that more recent data is available in smaller discrete time units (that is, higher granularity) than data from the more distant past. For example, in the following default configuration:

The Repository data allows you to see enterprise-level performance at a glance. You can determine the root cause of performance deviations, and identify changes to your SQL Server® environment quickly and easily.

There are five types of data tables:

There are three levels of aggregation:

There are six levels of granularity and age. For details, see Understanding Time Ranges and Data Intervals.

For each type, level, and granularity described in Understanding Time Ranges and Data Intervals there is a corresponding table in the repository. The naming convention for each table is: pass_{level}_{type}_fact_{granularity}.

Purging of data of the first level of granularity — 1m — is performed by deleting the whole table and not rows in the table. Therefore, the table of the previous age is also kept. For these tables we add a timestamp of the creation time at the end of their name.

In addition, there is another table of samples that has its own retention of three days: pass_request_stat_fact_1m.

Additional types of data, which are stored in different tables, include: batch, syntax, plan, object, and file. For each of the these tables there are two granularities: 1m and permanent. Besides file type, there is a table for each type and granularity. The naming convention for each table is: pass_{type}_dim_{granularity}. Management of the 1m granularity is the same as described in Understanding Time Ranges and Data Intervals.

SQL Statements text, Batch text, and Plan text is captured separately from other types of information and is stored separately.

This is done to ensure that the same text is not saved multiple times, and also to simplify the collection of this data which is expensive to gather and save.

Retention period for these texts is not handled by the time pyramid described in Understanding Time Ranges and Data Intervals. They are saved for as long as the dimensions referring to them are kept in the repository.

Data is held in tables called pass_{type}_dim where {type} is either syntax (SQL statements), Batch, or Plan.

Each text is saved just once based on its key. However, for Plans, there are times that different plan handles may actually hold the same plan text xml. For that reason, Foglight computes a plan_text_hash for each plan to indicate those plans that differ only in their plan handle.