ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

How does CCS 12.5.x determine when the database maintenance plan is due.

book

Article ID: 158440

calendar_today

Updated On:

Products

Control Compliance Suite Control Compliance Suite Standards Server

Issue/Introduction

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.

Environment

CCS 12.5.x

Resolution

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.

 

 

 

 

 

Attachments

GetPhysicalIndexOfInterest-CSM_Reports.sql get_app
GetPhysicalIndexOfInterest-CSM_DB.sql get_app