Operations Manager 2012 R2 – Data Warehouse can’t process performance data

We had the problem that our data warehouse can’t process all performance data being collected. To get rid of this problem you need to disable some performance collection rules in your opsmgr 2012r2 (preferable the ones you don’t need).

You may find out which one collect most data you can run the following queries against your dwh.

Performance insertions per day

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeSampled, 102)) = 1)
THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeSampled, 102)
END AS DaySampled, COUNT(*) AS PerfInsertPerDay
FROM PerformanceDataAllView with (NOLOCK)
GROUP BY CONVERT(VARCHAR(20), TimeSampled, 102) WITH ROLLUP
ORDER BY DaySampled DESC

To find the rules collecting the most Performance Signature data in the database

select managementpack.MPName, ruleview.DisplayName,
count(*) AS TotalPerfSig
from performancesignaturedata with (nolock)
inner join performancesignaturehistory with (nolock)
on performancesignaturedata.performancesignaturehistoryid = performancesignaturehistory.performancesignaturehistoryid
inner join performancesignature with (nolock)
on performancesignaturehistory.performancesignatureid = performancesignature.performancesignatureid
inner join ruleview with (nolock)
on ruleview.id = performancesignature.learningruleid
inner join managementpack with(nolock)
on ruleview.managementpackid = managementpack.managementpackid
group by managementpack.mpname, ruleview.Displayname
order by TotalPerfSig DESC, managementpack.mpname, ruleview.DisplayName

Good luck