Daily state aggregations appear to be stored against the wrong date in the data warehouse database when the time zone of the server hosting the SQL Server instance is at an offset to UTC. This is evident in the UK, because for approximately half the year the UK is on UTC, at which point the daily aggregations are stored against the correct date, but during BST (UTC+1) they are stored against the previous date. I suspect this may only affect time zones at a positive offset to UTC, and that the problem is in
dbo.StandardDatasetAggregate, possibly related to the calculation of
@IntervalStartDateTime, but I haven’t been able to pinpoint the bug. I also haven’t checked whether the same situation exists with the other aggregate types, such as performance.
The problem can be best illustrated with a script, which works against a database running in the UK for web application transaction monitor-data generated in 2014:
USE OperationsManagerDW -- Determine a web application transaction monitor with relevant data, i.e. during BST and where an unhealthy state has been recorded. DECLARE @ManagedEntityMonitorRowId INT, @Date DATE SELECT TOP 1 @Date = sdf.[Date], @ManagedEntityMonitorRowId = sdf.ManagedEntityMonitorRowId FROM dbo.vStateDailyFull sdf INNER JOIN dbo.vManagedEntity me ON sdf.ManagedEntityRowId = me.ManagedEntityRowId AND me.FullName LIKE 'WebApplication[_]%' AND me.FullName NOT LIKE '%WatcherComputersGroup' WHERE sdf.MonitorRowId = ( SELECT TOP 1 MonitorRowId FROM dbo.vMonitor WHERE MonitorSystemName = 'System.Health.EntityState' ) AND ( sdf.InYellowStateMilliSeconds > 10000 OR sdf.InRedStateMilliseconds > 10000 ) AND sdf.[Date] BETWEEN '31/Mar/2014' AND '25/Oct/2014' -- ~BST. ORDER BY sdf.[Date] -- Return the data. SELECT * FROM dbo.vStateFull WHERE ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId AND [DateTime] >= @Date AND [DateTime] < DATEADD(d, 2, @Date) ORDER BY [DateTime]
For a particular web application transaction monitor, my results for the daily aggregation (
AggregationTypeId = 30) for 31/03/2014 show that
206173, but the sum total of
InYellowStateMilliseconds for the hourly aggregations (
AggregationTypeId = 20) is
0 for this date. The figure of
206173 shows against the hourly aggregations associated with the following day, 01/04/2014.
I have logged this bug on Microsoft Connect.