Control Compliance Suite (CCS)

CCS has the following feature: "You can configure CCS to send an email notification, and display an alert on the Product Health and Status area of the CCS Console Homepage, when the database maintenance plan is due or the CCS Manager needs attention. A notification is sent and an alert is displayed, if the index fragmentation in CCS databases goes above 90% and when the alert notification is due as per the number of days specified in the Health and Status Notifications configuration. High index fragmentation can negatively affect performance of the activities that depend on the databases."

This article explains how CCS determines when the database maintenance plan is due, how it calculates the 90% value.

CCS 12.6.x

The list of tables whose indexes are candidates for triggering DB maintenance are available in ADAM  (internal to CCS).

For CSM_DB tables, the DN is:

CN=Data,CN=«GUID»,CN=PDBTablesCandidateForHighIndex,CN=1.0.0.0,CN=PDB,CN=IndexFragmentation,CN=EmailNotification,CN=HealthAndStatus,CN=Global,CN=Config,OU=Infrastructure,O=Symantec

The value is: dbo.R_AssetStandardSummary, dbo.R_AssetSummary, dbo.R_CheckResults, dbo.R_Checks, dbo.R_ChecksToSections, dbo.R_ChecksToStandards, dbo.R_JobComplianceScores, dbo.R_JobCompositeScores, dbo.R_Sections, dbo.R_StandardFlat, dbo.R_Standards, dbo.B_DataImports, dbo.B_FailureAsset, dbo.B_Jobs, dbo.B_JobsToStandards, dbo.B_ReportJobs, dbo.B_Reports, dbo.B_Standards, dbo.B_DataImports, dbo.Asset.ScoreLastModifiedDate, dbo.Asset.Scores

For CSM_Reports tables, the DN is:

CN=Data,CN=«GUID»,CN=RDBTablesCandidateForHighIndex,CN=1.0.0.0,CN=RDB,CN=IndexFragmentation,CN=EmailNotification,CN=HealthAndStatus,CN=Global,CN=Config,OU=Infrastructure,O=Symantec

The value is: dbo.SubjectTestResult*, dbo.HierarchyRelationship, dbo.SubjectAttribute, dbo.TestAttribute, dbo.vUserAsset, dbo.ivAssetName

Whenever average index fragmentation of these table indexes reaches 90% for either PDB (CSM_DB) or RDB (CSM_Reports) or both, the “Database maintenance is pending” will be shown.

NOTE: The average fragmentation has to cross 90% for the "Database maintenance is pending” link to be visible. If average fragmentation is less than 90% the “Database maintenance is pending” link will not be shown.
 

To manually query what the percentage of each of these indexes is at, two SQL scripts are attached to this article. The scripts can be run from the Microsoft SQL Server Management Studio and will do a query showing the ObjectName, IndexName and IndexFragmentation. One script for the CSM_DB, one for the CSM_Reports database.