|
|
 
0 |
As we continue looking through the causes of undesirable database growth trends in the OperationsManager and OpsMgr data warehouse, we're going to have a look at unit monitors. As mentioned, previously, as I am sorting through some of this information with OpsMgr administrator asking for help, I'll post the methodologies here. Do leave comments if you have useful input to add to the process.
In part 1, we looked at the most common events as a possible source of database growth: OpsMgr Database Hygiene: Monitoring database insertions to keep your infrastructure tuned
In part 2, we discussed table size as an indicator of what type of data is causing unwanted database growth: OpsMgr Database Hygiene Part 2: Table Size Reporting and what this information reveals...
In this post, we'll assume that the StateChangeEvent table popped up as a large table in the "Large Table Query from part 2 of this series. When identifying growth in state change data, it's important to be sure we're experiencing a spike in state changes in recent days. Data is not groomed for disabled monitors last I checked, which means you could have a lot of old state data from one or more problematic unit monitors that you disabled.
State Changes Per Day
We can start by looking at the number of state changes by date to see if this growth is recent, and thus the source of the database growth. If the state change count was low for all recent dates, then you'd want to look elsewhere for the source of recent OpsMgr database growth.
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeGenerated, 102)) = 1)
THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeGenerated, 102)
END AS DateGenerated, COUNT(*) AS StateChangesPerDay
FROM StateChangeEvent WITH (NOLOCK)
GROUP BY CONVERT(VARCHAR(20), TimeGenerated, 102) WITH ROLLUP
ORDER BY DateGenerated DESC
If the state change data is not current, Kevin has a SQL script to clean up the old data HERE. Take care of this before returning to the large tables query.
Top 10 Noisiest Unit Monitors in Database (per Object) (from Kevin Holman - found HERE)
And this query will present the noisiest monitors in terms of the number of state changes. This could contain monitors presenting old state data. If you run Kevin's query to clean up old state data, then re-run this query, results should reflect unit monitors with large numbers of state changes within the grooming period.
select distinct top 10 count(sce.StateId) as NumStateChanges, m.MonitorName, mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join monitor m with (nolock) on s.MonitorId = m.MonitorId
join managedtype mt with (nolock) on m.TargetManagedEntityType = mt.ManagedTypeId
where m.IsUnitMonitor = 1
group by m.MonitorName,mt.typename
order by NumStateChanges desc
In the Next Installment...
In the next post (later this week), we will look into noisy rules along with rules and monitors generating the most alerts.