Eric, a few things on this:

The ManagedEntityProperty keeps a record, of every instance of each class, and all discovered properties of those classes, over time.  For instance – if you have a windows computer, and you change the IP address, there will be two records in the MEP table for that Windows Computer instance, one with each IP address.  If you have volatile discoveries (see my “config churn” article) then this table will grow very large, often prematurely.  Old Dell and IBM MP’s were famous for filling this up – along with some MS MP’s like DNS, DPM, and SQL.

1.       It is “typical” for the ManagedEntityProperty table to be the largest table in the warehouse.

2.       By default in SP1 – we didn’t groom this table… so it would never cleanup or shrink.  I don’t think this grooming got added in R2, so it is very possible that we still don’t groom it.

3.       It generally isn’t a big deal to have a bloated MEP table… the only time I have seen issues is when complex custom reports query it.

What can you do????  (remember – you don’t HAVE to do anything… this generally isn’t a big deal.  However, if you have more than 400 days, AND/OR suffer from a VERY large MEP table and require to reduce the amount of space it retains):

1.       Run the queries to find out your historical largest consumer of MEP.

select distinct mep.ManagedEntityRowId, me.FullName, Count(mep.managedEntityPropertyRowId) as 'Total'

from ManagedEntityProperty mep WITH(NOLOCK)

LEFT JOIN ManagedEntity me WITH(NOLOCK) on mep.ManagedEntityRowId = me.ManagedEntityRowId

group by mep.ManagedEntityRowId,me.FullName

order by Total desc

2.       Run the queries to find out if this is still a current problem – and fix it.

(DW) Top discovery rule in the last 24 hours:

select ManagedEntityTypeSystemName, DiscoverySystemName, count(*) As 'Changes'

from

(select distinct

MP.ManagementPackSystemName,

MET.ManagedEntityTypeSystemName,

PropertySystemName,

D.DiscoverySystemName, D.DiscoveryDefaultName,

MET1.ManagedEntityTypeSystemName As 'TargetTypeSystemName', MET1.ManagedEntityTypeDefaultName 'TargetTypeDefaultName',

ME.Path, ME.Name,

C.OldValue, C.NewValue, C.ChangeDateTime

from dbo.vManagedEntityPropertyChange C

inner join dbo.vManagedEntity ME on ME.ManagedEntityRowId=C.ManagedEntityRowId

inner join dbo.vManagedEntityTypeProperty METP on METP.PropertyGuid=C.PropertyGuid

inner join dbo.vManagedEntityType MET on MET.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId

inner join dbo.vManagementPack MP on MP.ManagementPackRowId=MET.ManagementPackRowId

inner join dbo.vManagementPackVersion MPV on MPV.ManagementPackRowId=MP.ManagementPackRowId

left join dbo.vDiscoveryManagementPackVersion DMP on DMP.ManagementPackVersionRowId=MPV.ManagementPackVersionRowId

AND CAST(DefinitionXml.query('data(/Discovery/DiscoveryTypes/DiscoveryClass/@TypeID)') AS nvarchar(max)) like '%'+MET.ManagedEntityTypeSystemName+'%'

left join dbo.vManagedEntityType MET1 on MET1.ManagedEntityTypeRowId=DMP.TargetManagedEntityTypeRowId

left join dbo.vDiscovery D on D.DiscoveryRowId=DMP.DiscoveryRowId

where ChangeDateTime > dateadd(hh,-24,getutcdate())

) As #T

group by ManagedEntityTypeSystemName, DiscoverySystemName

order by count(*) DESC

3.       Change your grooming (manual groom) of MEP

a.       To find out how old your MEP data is: 

DATEDIFF(d, MIN(toDateTime), GETDATE()) AS [Current] FROM ManagedEntityProperty

b.      To manually groom MEP data (DON’T GROOM MUCH at a time at first – this can take many, many hours and requires lots of free space for transaction logs.

You can kick it off manually (replace @DataMaxAgeDays value of 400 with your own setting – starting with the same value as your total retained days, then reduce this number gradually.  Even 1 day can take considerable time and transaction log space to groom):

Declare @DataMaxAgeDays Int

select @DataMaxAgeDays = 400

        -- delete old MEP rows

        DELETE ManagedEntityProperty

        WHERE (ToDateTime < DATEADD(day, -@DataMaxAgeDays, GETUTCDATE()))

        -- delete old RP rows

        DELETE RelationshipProperty

        WHERE (ToDateTime < DATEADD(day, -@DataMaxAgeDays, GETUTCDATE()))

Good Day,

I recently discovered Kevin’s post on Useful Operations Manager 2007 SQL queries and when running the “Large table Query” on the DW our largest table is  ManagedEntityProperty just over 12Gig.  I am wondering if it is kosher for this table to be the largest and if not is there a method for determining why this specific table is so large?  We are monitoring just over 400 servers no ACS with only a few added management packs from the standard install DHCP,DNS and ISA still using SP1.

I have already been through the OpsDB and cleaned up the LocalizedText table that one was a life saver for console performance Kudos Kevin!

Thanks,

Eric Porter

System Administrator

McCain Global Technology Center

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/momlist/

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/momlist/