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.
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.
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).
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:
And the same chart, also showing 24 hours but with 60 minute 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:
and the same report but at 60 minutes interval download 1824 rows from the performance counters table:
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.