Modifying grooming settings for the OpsMgr databases

Modifying grooming settings for the OpsMgr databases

OpsMgr 2007 includes three databases: Operational, Data Warehouse, and the ACS database (assuming you have implemented ACS). We discuss how to change the grooming settings for each.

Operations database

The Operational database is the most straightforward of the three, as you can change the settings in the Operations console in the Administration node, under Settings -> General -> Database Grooming. Note that there are multiple data types, each having its own setting. This becomes more significant when we look at grooming the data warehouse.

The default setting for each of the data types is to remove or groom the data after seven days.

Data Warehouse database

The console does not have an interface to modify data retention settings for the data warehouse. You can groom the data warehouse settings by modifying columns in the StandardDatabaseAggregation table in the Data Warehouse database.

By default, data is groomed out at different intervals depending on the degree of aggregation. Data is stored by type, and the ranges for data retention vary from 10 days to 400 days depending on the range of data. You can view the grooming settings by running the following SQL query:

USE OperationsManagerDW

SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation

The default settings returned by this query are displayed in the following table:

AggregationInterval
DurationMinutes

BuildAggregationStored
ProcedureName

GroomStored
ProcedureName

MaxData
AgeDays

GroomingInterval
Minutes

MaxRows
ToGroom

NULL

NULL

EventGroom

100

240

100000

NULL

NULL

AlertGroom

400

240

50000

NULL

NULL

StateGroom

180

60

50000

60

StateAggregate

StateGroom

400

60

50000

1440

StateAggregate

StateGroom

400

60

50000

NULL

AEMAggregate

AemGroom

30

240

100000

1440

AEMAggregate

AemGroom

400

240

100000

NULL

PerformanceAggregate

PerformanceGroom

10

240

100000

60

PerformanceAggregate

PerformanceGroom

400

240

100000

1440

PerformanceAggregate

PerformanceGroom

400

240

100000

To make some sense of this, consider the following:

  • The first column (AggregationIntervalDurationMinutes) is the interval in minutes that data is aggregated. NULL is raw data, 60 is hourly, and 1440 is daily. You can see that some performance data is not aggregated at all, some is on an hourly basis, and some is daily.
  • MaxDataAgeDays is the maximum number of days data is retained. Depending on the type of data and its degree of aggregation, defaults can range from 10 to 400 days. This is the value we will be modifying, based on the particular type of data (Events, Alerts, State, AEM, or Performance data), and level of aggregation.
  • GroomingIntervalMinutes is the grooming process frequency, or how often the groomed stored procedure runs. Performance, Alert, Event, and AEM data is groomed every 240 minutes (4 hours); the procedure to groom State data runs every hour.
  • MaxRowtoGroom is how many rows are processed in a given execution of the grooming procedure.

As an example of how this works, let’s look at non-aggregated Event data, which is the first row of information. We know that this pertains to Events information because of the referenced procedure name EventGroom (GroomStoredProcedureName). The query tells us that Event data is not aggregated (AggregationIntervalDurationMinutes=NULL) and is saved for 100 days (MaxDataAgeDays). The EventGroom procedure grooms data (GroomStoredProcedureName), and runs every 240 minutes/4 hours (GroomingIntervalMinutes). Each time the stored procedure runs, it will groom a maximum of 100,000 rows.

The following SQL code changes the grooming frequency for Event data:

USE OperationsManagerDW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = <number of days to retain data>

WHERE GroomStoredProcedureName = ‘EventGroom’

Changing the retention period for Event data is relatively easy; since it is never aggregated, it only has one row in the StandardDatsetAggregation table. The following syntax can be used as a basis for updating retention periods for other types of data:

USE OperationsManagerDW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = <number of days to retain data>

WHERE GroomStoredProcedureName = ‘<procedure name>’ AND AggregationIntervalDurationMinutes = ‘<aggregation interval duration>’

Using the above example, let’s look at how to to change the grooming frequency for hourly groomed performance data as an example of how to change retention for a data type that has multiple aggregation levels. We will update the grooming period for Performance data that is aggregated on an hourly basis:

USE OperationsManagerDW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = <number of days to retain data>

WHERE GroomStoredProcedureName = ‘PerformanceGroom’ AND AggregationIntervalDurationMinutes = ’60′

Be aware that as OpsMgr aggregates most of the data in the Data Warehouse database, its growth on a day-to-day basis is less than the Operational database. However, since the retention period is longer, it will grow to be considerably larger, as you can see from our earlier blog entry, Estimating Database Sizes for OpsMgr 2007 (http://ops-mgr.spaces.live.com/default.aspx?_c01_BlogPart=blogentry&_c=BlogPart&handle=cns!3D3B8489FCAA9B51!171).

*** As an update, if you are running OpsMgr 2007 SP1 and OpsMgr 2007 Reporting SP1, you can use the Data Warehouse Data Retention Policy Tool (dwdatarp.exe) to view and configure data warehouse data retention policies. Instructions for this command line tool and the download are available at http://blogs.technet.com/momteam/archive/2008/05/14/data-warehouse-data-retention-policy-dwdatarp-exe.aspx. Thanks to Daniel Savage of the OpsMgr team for providing this! ***

ACS database

Data is groomed out of the ACS Database based on the data retention period specified during setup, with the default being 14 days. After looking at what it takes to groom the data warehouse, ACS is relatively simple! The ACS Collector calls a SQL procedure to remove a partition that is outside of the data retention period. This procedure can be found at %SystemRoot%\system32\security\adtserver\DbDeletePartition.sql. The data retention period itself is specified in the dtConfig table in the OperationsManagerAC database.

To update the data retention period, run the following SQL query:

USE OperationsManagerAC

UPDATE dtConfig

SET Value = <number of days to retain data + 1>

WHERE Id = 6

To retain 7 days of data, set <Value> = 8. Data is accumulated at approximately 7.6 MB per day per workstation.

About these ads
This entry was posted in Tuning and Configuration. Bookmark the permalink.

2 Responses to Modifying grooming settings for the OpsMgr databases

  1. william says:

    How could I control at which hour the grooming process takes place?
     
    Is it from the tieme at which the settings change is done?
     
    What would be the impact in performance of i.e. changing grooming settings from 4 to 12 hours?
     
    Thanks for your help.
    WG.

  2. Operations says:

    MOM 2005 had a nightly job you could change the timing for that moved data from the Operational database (OnePoint) to the Reporting database. There are no visible jobs with OpsMgr 2007 – all database maintenance, including grooming, is performed by stored procedures within the respective databases.
     
    Microsoft does not publish the database schema, and direct edits to the database tables are not supported. It will take a bit of digging for me to find where all this is. A question would be is are you having a production impact from the grooming jobs running when they do? In general, most of the overhead from IO is actually database reads, not writes, and latency occurs when you have slow disks. Walter Chomak just wrote a very nice article on this, see http://wchomak.spaces.live.com/blog/cns!F56EFE25599555EC!610.entry for details.
     
    As far as your third question, the impact of grooming less often means you incur the overhead less often, but the jobs will run longer.
     
     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s