UIM product data_engine and SQL maintenance jobs

book

Article ID: 222888

calendar_today

Updated On:

Products

DX Infrastructure Management CA Unified Infrastructure Management for z Systems CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM) NIMSOFT PROBES

Issue/Introduction

Our DBA team is requesting if the UIM application has the ability where we can disable/turn off your UIM Product MSSQL "maintenance" jobs that run daily/weekly so they do not conflict with our in-house developed DBA MSSQL maintenance jobs.  Our DBA team would like to manage SQL maintenance type jobs, for example compressing the DB or rebuilding index's.

Environment

Release : 20.3

Component : UIM - DATA_ENGINE

Resolution

First,

please review this KB Article:

How does data_engine maintenance and retention work?
https://knowledge.broadcom.com/external/article/33704/how-does-data_engine-maintenance-and-ret.html

The data_engine indexing option was added to the data_engine a long time ago, but in large environments I would not recommend using it. In fact the data_engine developer that originally added that option intended to remove it some time ago but it still remains.

The data_engine index maintenance option, when enabled and run by  the data_engine, only runs on the RN, HN, BN, DN, VN tables. 

RN = Raw data
HN = Historical data
DN = Daily (data aggregation/summary)
BN = Baseline data
VN = View tables used specifically for CABI

Second,

I would NOT recommend disabling the data_maintenance option 100%. In the Raw Configuration, "data_management_active" should be set to yes, otherwise you would be cicumventing our recommended best practices when it comes to data maintenance. That said, there may be customers who have disabled data maintenance and worked around it by running their own procedures.

Third,

in general, I would recommend the following:

-> Enable Partitioning (MS SQL Server Enterprise Edition only)
-> Disable 'Index Maintenance' option to stop automatic reindexing (run a daily defrag job on select tables instead)
 
In the Raw Configuration of the data_engine this is the same as the parameter-> auto_reindex = no

To enable partitioning, access the data_engine configuration in the Admin Console to turn on partitioning for Microsoft SQL Server Enterprise Edition.

Please refer to and review:

Manually partitioning the UIM database for Microsoft SQL Server
https://knowledge.broadcom.com/external/article/34658/

Based on our experience with large UIM environments/implementations, when you have DB partitioning enabled, there is really NO significant gain in performance and no advantage in defragging the table indexes, especially for large tables. Implementing partitioning is sufficient for optimizing performance.

For instance, large tables with high index fragmentation may defrag down to 30-70% fragmentation, but in a few days the fragmentation returns, e.g., on the NAS backend tables. As long as you are using partitioning, there is no need to defrag the RN, HN, etc tables, but...the specific tables I listed below MUST be defragged each day (during non-business hours if possible), by running a daily defrag job on them. There are a few options in how to do that but a DBA would know. If you need suggestions, just let me know but web search can show you how it can be done each day. Also, please make sure your data_management_timeout parameter in the data_engine is set to 65536 (not the default of 7200 which is only 2 hours). 

These are the key tables with indexes that require defragmentation (DAILY). Ask your DBA to set up a job to run this DAILY (off-hours) index defrag job.

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;
ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;
ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;
ALTER INDEX ALL ON S_QOS_DATA REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;
ALTER INDEX ALL ON NAS_ALARMS REBUILD;

SQL Server Memory Tuning
====================================================================

Ask your DBA to keep an eye on MS SQL Server memory pressure to see if you need to add some more memory for ‘breathing room' so any/all jobs can complete without issues/hanging.

Last,

Compression

Compress data before delete (enabled by default in the data_engine):

If selected, compression is done on raw data and copied into historic tables before a delete is performed. And historic data is compressed and aggregated into Daily Data, before deleting it from the HN tables.

Data Maintenance activities

During scheduled maintenance runs, the data_engine probe compresses and aggregates raw data from RN tables into hourly data that is stored in the historic data (HN) tables.

data_engine ‘Compression’ simply means it 'summarizes' or 'rolls up' data.

An orthogonal configuration that you can set up is to enable MS SQL Server row or page level compression.

This is a DB configuration parameter that can be enabled through the data_engine config in AC, but the process itself is transparent.

- Older RN data is purged based on a user-defined period (raw data retention setting)
- Then the DE compresses and aggregates hourly data from HN tables into daily data that is stored in DN tables
- Older HN data is purged based on a user-defined period
- The last sample value coming from the probes for each QoS object is collected and populated in the S_QOS_SNAPSHOT table which is used to provide fast QOS data access for webapps, views/reports and dashboard data.

Real compression happens during data maintenance.  You will see in the tbnlogging talble the actual commands that were run to compress.

Compression mode:
Compression is generally a good thing if you have enough CPUs/processing speed for it.  It’s only compressing indexes, however.

Query to show Index compression settings

SELECT  SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] 
       ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] 
       ,[rows] 
       ,[partition_number]
       ,[data_compression_desc] 
       ,[index_id] as [IndexID_on_Table]
FROM sys.partitions 
INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id 
--WHERE data_compression > 0 
           AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' 
           AND OBJECT_NAME(sys.objects.object_id) = 'RN_QOS_DATA_0001'
ORDER BY SchemaName, ObjectName

Query to show Table compression settings

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] = 1
      and [t].[name] = 'RN_QOS_DATA_0001'

If you follow the approach and recommendations above, normally you can go 2-3 years without worrying about the data management/maintenance unless for some reason 1 or more jobs started to fail and you werent aware of it.

And of course, as always, take a backup/snapshot of the UIM database before making any significant changes.