UIM Best Practices - Indexes that require defragmentation (daily) - SQL Server

Products

• NIMSOFT PROBES
• DX Infrastructure Management

Issue/Introduction

UIM Best Practices - Indexes that require defragmentation (daily) - SQL Server

Environment

Release : Any UIM Release

SQL Server DB

Resolution

Index maintenance option, when enabled and run by  the data_engine, only runs on the RN, HN, BN, DN, VN tables. That said, with large UIM environments/implementations, when you have partitioning enabled, there is really no gain in performance or advantage in defragging the table indexes, especially for large tables. For example, large tables with high index fragmentation may be defragged down to 30-70% fragmentation, but in a few days the fragmentation would return. As long as you are using partitioning, there is no need to defrag the RN, HN etc tables, but...the 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, make sure your data_management_timeout 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.

 

Indexes that require defragmentation (daily)

Here is a list of tables that you CAN run a daily index defrag job upon:

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;

Additional Information

How to check the DB Fragmentation level for SQL Server

https://knowledge.broadcom.com/external/article?articleId=73421