Return
-
Title
Querying SQL queries collected by SQL PI -
Description
Is it possible to query all of the SQL queries running collected by SQL PI? -
Resolution
All of the SQL PI data collected is stored in the SQL PI repository that is a SQL Server database.The most recent SQL PI Schema reference guide was made for Infobright in the 5.7.5.35 release. Attached to this Knowledgebase article is a copy of this guide. This has all of the table information.The main difference since the change from Infobright to SQL Server is that the data is then combined from the historical data which is spread across 30 tables for each rollup depending on the range displayed in the pagepass_instance_stat_fact_1m_YYYYMMDDHHHH (4 of these for 6 hours) … these roll up every 6 hourspass_instance_stat_fact_1h_pXX (30 of these)pass_instance_stat_fact_1d_pXX (30 of these)pass_instance_stat_fact_1w_pXX (30 of these)Instance Key can be used to filter the agent (pass_instance_dim)The specific queries should be joined on either the SQL Handle or the Query HashSpreading the data across 30 tables (Pxx) is just a way to spread the activity of instance between multiple tables. XX is calculated as instance_id mod 10. Since we have limit of 300 instances per database, and we have 30 of these tables – that means each table will hold data for up to 10 instances.STATUS
Enhancement Request FOM-369 has been filed previously to create an updated schema guide. -
Defect ID
FOM-369 -
Attachments