SQL Server SCOM: Many perf.perfHourly and State.StateHourly tables in OperationManagerDW

By a customer, the SCOM’s Database OperationManagerDW growth fast and becomes very big.

To see what’s happens, in SSMS (SQL Server Management Studio), I right-click on the database open Reports>Standard Report> Disk Usage by Top Tables:
You have many interesting reports with SSMS and I advise you to use it.
In my case the “Disk Usage by Top Tables” report displays me many perf.perfHourly and State.StateHourly tables with a _guid():

After reading some blog’s, the most interesting blog was the blog from Kevin Holman about “Understanding and modifying Data Warehouse retention and grooming”
My goal is just to share, how I use this good blog to help my customer.
Using the query to retrieve the information like this one below, I discuss with the SCOM responsible:

SELECT DataSetDefaultName, AggregationTypeId, MaxDataAgeDays FROM StandardDatasetAggregation sda INNER JOIN dataset ds ON ds.datasetid = sda.datasetid ORDER BY DataSetDefaultName

According to the Scom Application owner, we apply some recommendations.
The first settings was apply by the SCOM responsible on the SCOM server side:

dwdatarp.exe -s <sqlservername> -d OperationsManagerDW -ds "Performance data set" -a "Hourly aggregations" -m 60

dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "Performance data set" -a "Daily aggregations" -m 180

dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "Alert data set" -a "Raw data" -m 100

dwdatarp.exe -s <sqlservername> -d OperationsManagerDW -ds "Event data set" -a "Raw Data" -m 10

dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "State data set" -a "Raw data" -m 60

dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "State data set" -a "Hourly aggregations" -m 60

dwdatarp.exe -s <sqlservername> -d OperationsManagerDW -ds "State data set" -a "Daily aggregations" -m 90

In a second time,  I run these queries to change some retentions:

--Alert Data

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 100 --default is 400

WHERE GroomStoredProcedureName = 'AlertGroom'

--Event Data

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 10 --default is 100

WHERE GroomStoredProcedureName = 'EventGroom'

--Performance HOURLY

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 60 --default is 400

WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationTypeId = '20'

--Performance DAILY

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 180 --default is 400

WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationTypeId = '30'

--State RAW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 60 --default is 180

WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationTypeId = '0'

--State HOURLY

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 60 --default is 400

WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationTypeId = '20'

--State DAILY

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = 90 --default is 400

WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationTypeId = '30'

After these changes, the database wasn’t growing fast and no more so many perf.perfHourly and State.StateHourly tables with a _guid().

I do this blog to share my experience with the SCOM database OperationManagerDW and give you some tips to have an optimized database for SCOM.

Thanks  to Kevin Holman for your help!

I hope this blog can help other DBA to do the same to not have a big database for nothing… 😉