UIM Database growth in size after upgrade to 20.33/20.4
search cancel

UIM Database growth in size after upgrade to 20.33/20.4

book

Article ID: 240951

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Database team sent us an email commenting that the UIM database was growing since 4/21, which was the day when we performed the upgrade to 20.3.3 without issues. Can you please let us know if its a normal behavior? We did not experience any issues from application side, but please let us know, so we can answer to DB team.

Environment

Release : 20.3

Component : UIM - INSTALL

Cause

- Unknown

Resolution

Check the size of these tables:

select count(*) from SSRV2AuditTrail
select count(*) from SSRV2AuditTrailModification
 
The results of these queries will be helpful too so please ask your DBA to run them and attach the results in .csv format WITH headers.
 
Query to list the top ‘N’ largest UIM tables
-- Adjust top value as needed 
 
SELECT TOP 10 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages,
    sum(a.used_pages) as UsedPages,
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND 
    i.index_id <= 1
GROUP BY
    t.NAME, i.object_id, i.index_id, i.name
ORDER BY
       SUM(p.rows) DESC
 
Query to determine which probes/sources are generating the MOST QOS
-- (This reflects the same output as clicking the data_engine ‘Status’ button but is adjusted for bigint data type to ensure results are returned)
 
create table #t (id bigint, name varchar(max), rname varchar
(max), hname varchar(max), dname varchar(max), bname
varchar(max),
rrows bigint, hrows int, drows int, brows int, rsize
int, hsize int, dsize int, bsize int)
insert #t (name,rname,rrows,id, hname, dname, bname)
SELECT sqd.name, object_name(i.object_id), sum(p.rows)
as rowCnt, sqd.qos_def_id,
replace(object_name(i.object_id),'RN','HN'), replace
(object_name(i.object_id),'RN','DN'), replace(object_name
(i.object_id),'RN','BN')
FROM
sys.indexes i INNER JOIN sys.partitions p ON i.object_id =
p.object_id AND i.index_id = p.index_id
INNER JOIN s_qos_definition sqd on
sqd.qos_def_id = cast(replace(object_name
(i.object_id),'RN_QOS_DATA_','') AS int)
WHERE object_name(i.object_id) like 'RN_QOS_DATA_%'
AND i.index_id <= 1
GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id
 
--After running the statement above, then run the SELECT below to see the results:
 
SELECT sqd.name as QOS_Object, object_name(i.object_id), sum(p.rows)
as Row_Count, sqd.qos_def_id,
replace(object_name(i.object_id),'RN','HN'), replace
(object_name(i.object_id),'RN','DN'), replace(object_name
(i.object_id),'RN','BN')
FROM
sys.indexes i INNER JOIN sys.partitions p ON i.object_id =
p.object_id AND i.index_id = p.index_id
INNER JOIN s_qos_definition sqd on
sqd.qos_def_id = cast(replace(object_name
(i.object_id),'RN_QOS_DATA_','') AS int)
WHERE object_name(i.object_id) like 'RN_QOS_DATA_%'
AND i.index_id <= 1
GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id
ORDER BY Row_Count DESC
 
Check space used
 
use CA_UIM
exec sp_spaceused
 
(As of 5/2/2022 at 3:00 PM)
CA_UIM Database Size is currently:
1657448.00 MB or 1657.448 GB, 209 GB Available
 
Customer must check the DB size in an hour and then tomorrow morning to make sure its not still growing at an unexpected rate. This was done and it remained the same then dropped.
 
Note for customer re question on best way to monitor the data_engine-> check out my UIM self-health monitoring KB at:
and try to get the SQL Server error generated when the username became corrupted, or use the sqlserver probe to monitor the DB connectivity using the check_dbalive checkpoint.
 
The hub queues looked fine and were processing data efficiently.
 
data_engine
We disabled index maintenance (It should not be enabled)
 
We recommend that the data_engine partitioning option should be set to enabled, but not until the largest tables have been manually partitioned first, then it can be enabled. This will optimize performance and keep the data_engine running smoothly and efficiently over time.
 
When partitioning is enabled, the only tables that require defrag are the following and this should be run as a daily job after hours.
This daily job should be run against the following tables which help maintain optimal performance in the Operator Console:
 
Use this for a defrag when the database is not as busy.
 
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;
ALTER INDEX ALL ON NAS_TRANSACTION_LOG REBUILD;
 
Also ensure there is enough memory allocated to the UIM database by analyzing 'memory pressure.' Ask the DBA to check.
 
Check the SQL Server Transaction logs / overall size.
 
The sudden post-upgrade increase in DB size did not continue and flattened out.
Powered by Wolken Software