“Today is Never in the Daily Tables” – How Precise Aggregates Data

by Apr 2, 2018

Precise’s historic repository, the Performance Management Database (PMDB) holds up to a three-year history of performance data by default. The data is stored in different tables representing different time periods, and it is worth understanding some of the subtleties of this.

The smallest granularity of saved data is called a “time slice.” A time slice represents either a 30-second span (for OS, Web, .NET, and J2EE), or 15-minute span (for most other technologies). Tables in the PMDB that contain time slice data all end with a “_T.” For example, each row of the PW_SQLS_LOCK_STAT_T table in the PMDB holds locking statistics for one 15-minute period.

There are corresponding tables for daily, weekly, and monthly (and a few hourly) tables as well. There is a PW_SQLS_LOCK_STAT_D table where each row contains locking statistics for one day. A “Summarize Data” task runs on the PMDB once per day early in the morning. It takes the previous day’s time slice data, aggregates it, and creates new entries in all of the daily tables (_D). At the end of the week, or month, the weekly (_W) and monthly (_M) tables are populated.

One consequence of this is that the daily tables aren’t populated until after the day is over, and weekly and monthly tables aren’t populated until the week or month is over. As I write this, it is 2:17 PM on March 30. I have a Precise installation (using SQL Server) running on my laptop, and it is, once per second, sampling SQL Server activity, and once every 15 minutes, saving that performance activity in the PMDB in the time slice tables. However, this data won’t be copied into the daily tables until after today is over and a row is inserted into all of the daily tables for “March 30.” If I go right now to the Activity workspace for Precise for SQL, and I select a two-week time frame, Precise will automatically display data it pulls from the daily tables. I will not see data for today.

Normally this isn’t important or even noticed. It is more important when running raw queries against the PMDB, or when using Custom Reports. It is sometimes difficult to get queries and reports to exactly match the Precise GUI is showing. Being aware of the different tables and how they are populated helps clear up some of these misunderstandings. As I’ve said to many people to explain this, “Today is never in the daily tables.”

You can find more technical information about the PMDB, its maintenance, and the Precise tables in the “Precise Performance Management Database Application Programming Interface” reference manual. You can find that manual, along with all of the other Precise documentation, here: http://wiki.idera.com/display/Precise/View+all+Precise+9.8.x+PDFs.