DX UIM data_engine probe and SQL maintenance jobs
Article ID: 222888
Updated On:
Products
Issue/Introduction
Our DBA team is requesting if the UIM application has the ability where we can disable/turn off your UIM 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 or higher
- Component: UIM - DATA_ENGINE
Cause
- data_engine administration and maintenance
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 we do 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,
We do NOT recommend disabling the data_maintenance option 100%. In the Raw Configuration, so "data_management_active" should be set to yes. Otherwise, you would be circumventing recommended best practices when it comes to data maintenance. That said, there may be customers have worked with their DBAs to disable data maintenance and worked around it by running their own jobs/procedures.
Third,
in general, we would recommend the following:
-> 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 data_engine parameter-> auto_reindex = no
Please refer to and review:
Manually partitioning the UIM database for Microsoft SQL Server
https://knowledge.broadcom.com/external/article/34658/
Based on our field 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 to veery large tables.
Implementing partitioning is sufficient for optimizing DX UIM database 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 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, a 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 so the jobs can complete (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. For example ask your DBA about scheduling a job to rebuild the table indexes below.
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_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_ALARMS REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD;
SQL Server Memory Tuning
=======================
Ask your DBA to keep an eye on MS SQL Server 'memory pressure' over time to see if you need to add some more memory for ‘breathing room' so any/all jobs can complete without issues/hanging.
Lastly,
Compression
Compress data before delete (this is 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. This is based on the data retention settings in the data_engine.
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' the 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 the OC portal webapps, views/reports and dashboard data.
Real compression happens during data maintenance. You will see in the tbnlogging table the actual commands that were run to compress data.
Compression mode:
Compression is generally a good thing if you have enough CPUs/processing speed for it. However, it’s only compressing indexes.
Query to show Table 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 3+ years without worrying about the data management/maintenance unless for some reason 1 or more jobs started to fail/continued to fail and you werent aware of it and the tables grew without bounds.
And as always, take a backup/snapshot of the UIM database before making any significant changes.
Feedback
