A while back in September 2012 I posted about a bug in Exchange 2010 aggregations in the data warehouse(Troubleshooting Exchange 2010 MP aggregations). The issue is still there, at least until version 220.127.116.11.
The aggregation stored procedure “” has been improved but the performance culprit has not been solved, so I took the time to rewrite the hack for the latest MP version. If you compare the two versions you’ll se the new hack is much more lighter, in fact I use an undocumented feature of the StateChangeEventList stored procedure that actually does the selection job for me.
If called with a null value for the @ManagedEntityMonitorRowId parameter, the procedure checks for the existence of a temporary table called #ManagedEntityMonitor to filter the returned change events. All I had to do was to create such a table and load with the proper monitors Ids just before the original storeds procedure calls the StateChangeEventList stored procedure:
CREATE TABLE #ManagedEntityMonitor
INSERT #ManagedEntityMonitor (ManagedEntityMonitorRowId)
SELECT ManagedEntityMonitorRowId from Exchange2010.vAvailabilityObjListV14 WHERE Scope=0
INSERT #StateEvent (ManagedEntityMonitorRowId, [DateTime], OldHealthState, NewHealthState)
@ManagedEntityMonitorRowId = NULL
,@IntervalStartDateTime = @Stretched_StartDateTimeForHealthDataRetrieval
,@IntervalEndDateTime = @Stretched_EndDateTimeForHealthDataRetrieval
,@UseAggregatesToBuildStartOfIntervalStateInd = 0
,@AggregationCoverViewName = NULL
,@AggregationDateTime = NULL
The complete stored procedure is available in the DropBox repository under Scripts, I cannot post on Technet Gallery since I’m directly modifying code that I don’t own.
This posting is provided “AS IS” with no warranties, and confers no rights.