Report parameters, time slice and intervals

Report Start and End time

When retrieving performance data from SQLWATCH database we have to keep in mind the fact that this is a decentralised architecture meaning the performance is not being offloaded to a dedicated central repository but is hosted on the monitored instance and therefore we could be connecting to a production instance which could hold gigabytes of performance data. For obvious reasons we do not want to pull all the available data into PowerBI as this could have negative impact on the service.

SQLWATCH has been designed to address such scenario and thus it operates on time slice data. PowerBI reports offer a number of time parameters to minimise the impact of data retrieval.

SQLWATCH performance dashboard parameters

Behind the scenes this translates to a SQL BETWEEN operator:

WHERE [snapshot_time] > DATEADD(HOUR, -@report_window, @report_end_time) 
AND [snapshot_time] <= @report_end_time

where the @report_window is ReportAgeHours and @report_end_time is the EndTime in PowerBI

For example, to see last 24 hours of performance data starting now, we would put 24 in the ReportAgeHours parameter and GETDATE()  in the EndTime parameter field. To see different time slice we can simply put date time in the ISO format: ‘2018-11-25 13:00’ in the EndTime parameter field.

SQLWATCH time slice timeline

This approach allows two things to happen:

  • travel back in time and report on any time slice stored in the SQLWATCH database.
  • bring back latest data if GETDATE()  is specified in the EndTime field without changing any parameters (useful for your Morning checks).

Interval time

The third time related parameter is ReportIntervalMinutes which is the resolution (zoom) of the timeline and the delta calculation period for cumulative metrics.

For example, we may want a detailed overview of the last 4 hours of operation with interval time of 5 minutes, or a high level overview of the last 7 days with interval time equal to 1 hour. This approach reduces the number of data points and thus volume of data returned from the database.

This is best visible on the below charts.

First chart is showing 24 hours with 5 minute interval:

SQLWATCH 5 minute interval

And the same chart, also showing 24 hours but with 60 minute interval:

SQLWATCH 60 minutes interval

The below screenshots show the number of rows returned from the databases for the 5 and 60 minutes intervals over 24 hours period.

The 5 minute interval download over 21736 performance counters rows:

SQLWATCH interval rowcount

and the same report but at 60 minutes interval download 1824 rows from the performance counters table:

SQLWATCH interval rowcount

 

Conclusion

Long term storage is at heart of SQLWATCH design. To achieve this, we need an efficient way of getting the data out of the database. Different time intervals in combination with the time slicing allow very flexible data retrieval.

Leave A Comment

Author

SQLWATCH a1929ece64de0517e67d5f5a423b7ec3?s=72&d=mm&r=g
Marcin Gminski
Post Date
25th May 2020

Recent Posts