Huntland Services Ltd

Tel: +44 (0)1392-490518
Fax: +44 (0)1392-428003
Enquiries@huntland.co.uk

Display the Size and Activity of Mom Tables

 
Back Download This Article

 

Use of the MOM OnePoint tables varies enormously with Events, Alerts and SampledNumericalData (Performance data) showing the top activity.

This script reports the size of each table and its activity since the last table statistics were calculated.  Updating the statistics is performed by a Grooming Task at 01:00 in the morning, daily.

Tables that are very active may usefully be installed on separate physical disks for large databases in order to improve performance.

USE OnePoint
GO
DBCC updateusage(0) WITH COUNT_ROWS, NO_INFOMSGS 
select so.Name, si.reserved as '8k Pages Reserved',si.used as '8k Pages Used',si.rows as 'Number of Rows', si.rowmodctr as 'Activity'
from sysindexes si join sysobjects so on si.id=so.id 
where so.type='U' and si.indid in (0,1) 
order by si.reserved desc