MOM 2005 Onepoint DB Perf data tuning
The following should help MOM Admins get a good picture of what is going on in their onepoint database. It will require you to groom out all but one days worth of data in the onepoint database so you will be able to get a better picture of what is being captured on a daily basis. Once you know what is happening on a daily basis you should be able to identify the counters that are collecting too much data then turn off the rules that are collecting extra un-needed data. Unfortunately each management pack loaded into the MOM 2005 environment has the potential to add additional duplicate performance collection rules. The worst offenders are the duplicates found in the the following management packs, Exchange (any version), and SQL Server (any version) as they have rules similar to the base OS Management Pack for collecting basic perf counters for disk utilization, memory, and CPU.
1. Make sure DTS job has been run and is up to date (a must if you are using reporting)
2. Change the Global setting for database grooming to Groom Data older than the following number of days to 1, apply the changes.
3. Open the SQL Admin tool and run the SQL Job "MOMX Partitioning And Grooming" to groom old data out of the onepoint db.
4. Once that SQL job has completed find out whats going on with these Kevin Holman SQL Queries:
First look at the number of Perfmon data points your collecting by running this SQL Query
-- Kevins performance objects
select performanceobjectname, performancecountername, count(performanceobjectname) as 'count' from sdkperformanceview
group by performanceobjectname, performancecountername
order by 'count' desc
Second check what the individaul servers perfmon data points are looking like
-- noisy perf computers
select distinct server, count(*) as NumberOfOccurences
from SampledNumericDataPerformanceReportView
group by server
order by numberofoccurences desc
If there is a server with a large number of perf data points run this query to see what its collecting
Replace YOURSERVERNAMEHERE with the server name you want to look at the data points for.
-- kevins performace object query
select performanceobjectname, performancecountername, count(performanceobjectname) as 'count'
from sdkperformanceview
where computername = 'YOURSERVERNAMEHERE'
group by performanceobjectname, performancecountername
order by 'count' desc
After looking at this data you should be able to have a good idea what rules need to be tuned.
Hope this helps!