Tuning to save resources

Typically when tuning SCOM we talk about saving time and reducing alert noise, but today I’m going to make a quick post on saving database space through tuning. If you’d like to get an idea of what your current database usage is, take a look at this tool from scom2k7.com. This will give you a breakdown of where space is used in the SCOM Data Warehouse.

What can I tune?

Out of the box SCOM gives you some controls to tune retention settings. Kevin Holman details these on his blog, he also provides us with an average distribution we can use for the following example. Three large consumers of database space should show up in your list (as they do in our example).

• Performance data

  • As implied by the name, this holds the performance metric data collected by SCOM. Reducing the frequency of collection will reduce the size of the raw data. Stopping collection on a set schedule will reduce both raw and aggregated data sets.

  • Reductions in this data will be visible in performance graphing.

• Event data

  • The event data set contains all events collected and stored for later evaluation. These aren’t collected on a frequency, and as such have no aggregate options.

  • Reductions on this data will be visible in the SCOM Event View.

• State data

  • Historic monitoring state changes are stored in this data set. If you have frequently flapping monitors this set will grow larger.

  • I would not recommend making any modifications to this data collection.

Finding what to tune

Easy Tune provides a simple CSV interface to tune SCOM workflows, but blindly tuning them isn’t going to be of much help. In the below two sections I’ve detailed some steps on finding the largest offenders of usage.

Performance data

The below SQL query is designed to be run against your data warehouse and will provide you with the 50 highest generating performance Rules, as well as the Management Pack holding them. This will give you an idea of what’s generating the most entries.

--  Most common performance data by rule (this may take a very long time to run)
--  Returned will be a list of the 50 most commonly recorded performance and the Rule and Management Pack that raised them

select top 50 RuleSystemName, ManagementPackSystemName, count(1) [TotalPerf]
from Perf.vPerfRaw pvpr 
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId 
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
inner join dbo.vRule vprRule on vpr.RuleRowId = vprRule.RuleRowId
inner join dbo.vManagementPack vprMp on vprRule.ManagementPackRowId = vprMp.ManagementPackRowId
group by vpr.RuleRowId, RuleSystemName, ManagementPackSystemName
ORDER BY TotalPerf DESC

Event data

The below SQL query is designed to be run against your data warehouse and will provide you with the 50 most frequently collected events, as well as the Rule and Management Pack that produced them. This is a great place to start tuning out the biggest offenders.

--  Most common events by event number and raw event description (this may take a very long time to run)
--  Returned will be a list of the 50 most common events and the Rule and Management Pack that raised them

SELECT top 50 EventDisplayNumber, Rawdescription, evtRule.RuleSystemName, evtMp.ManagementPackSystemName, COUNT(1) AS TotalEvents 
FROM Event.vEvent evt 
inner join Event.vEventDetail evtd on evt.eventoriginid = evtd.eventoriginid 
inner join Event.vEventRule evtr on evt.EventOriginId = evtr.EventOriginId
inner join dbo.vRule evtRule on evtr.RuleRowId = evtRule.RuleRowId
inner join dbo.vManagementPack evtMp on evtRule.ManagementPackRowId = evtMp.ManagementPackRowId
GROUP BY EventDisplayNumber, Rawdescription , evtRule.RuleSystemName, evtMp.ManagementPackSystemName
ORDER BY TotalEvents DESC

Advanced tuning

Tuning by instance

Both of the above queries can be adjusted to pick out particularly noisy instances. Use the instance level overrides in Easy Tune if the data from these instances can be ignored.

Tuning by schedule

With performance in particular, the time of day the data is collected can greatly change its value. Use of the Scheduling Tool in Easy Tune can allow you to lower or stop collection during non-business hours or times when the data isn’t important.

If you would like to find out more about tuning SCOM and reducing alert noise, then take a look at our suite of tuning products - Easy Tune.