What does the data_engine index maintenance option do and should I enable it?
Release : 9.x or higher
Component : UIM - DATA_ENGINE
The data_engine indexing option was added to the data_engine a long time ago, but in large environments that utilize DB partitioning, it is not recommended nor necessary to enable it.
The index maintenance option, when enabled and run by the data_engine, only runs on the RN, HN, BN, DN, VN tables. In large UIM environments when you have partitioning enabled, as per testing, there is little to no significant gain in performance or advantage in defragmenting the RN, HN, BN, etc., table indexes, especially for large tables.
Note that defrag on large table indexes may take an extremely long time and/or not finish successfully. Defrag on a large table can take more than 18-24 hours and uses up significant DB Server resources to run. These jobs may also hang if the job does not have enough resources to be able to run to successful completion.
As confirmed during testing, large tables with high index fragmentation may be successfully defragged as a single job/separately, down to 30-70% fragmentation, but in a few days the high fragmentation will often return, e.g., back up to ~99.98%.
The good news is that, as long as you are using database partitioning, there is no need to defrag the RN, HN, BN, VN, etc. tables, but, some 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 available on how to run a daily job to defrag the table indexes. Consult with your DBA to decide on what method to use. Please make sure your data_engine’s data_management_timeout is set to 65536.
The data_engine table partitioning option is ONLY officially tested and supported in the Enterprise version, e.g., Microsoft SQL Server Enterprise Edition (NOT standard or other versions), even though the latest versions of the SQL Server Standard edition include the partitioning feature.
Ask your DBA to set up a job to run this DAILY table index defrag job (during non-business hours or during periods of low message traffic if possible).ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;
data_engine AC GUI Reference:
Note that in the AC/IM reference it states:
Note: "On very large tables (over 10 GB), running index maintenance may not complete in a reasonable amount of time. "
SQL Server Memory Tuning
Ask your DBA to analyze and keep an eye on MS SQL Server memory pressure to see if you need to add some more memory for ‘breathing room' so any maintenance jobs can complete without issue.