Table history tracking
Every user-created Incentives table (for example, data, custom, structural) has a corresponding history table to track record inserts, updates and deletes. Consider disabling History Tracking for any table that does not require audit history.
Disabling this feature essentially eliminates the SQL transactions going to the history table, which should improve insert, update, and delete times to the user-created table.
When the clear table operation is part of a nightly batch process, typically the tracking of history including inserts/updates/deletes is not needed. The recommendation is to disable History Tracking on the tables that are getting cleared on a regular basis. This will ensure that over time, the corresponding history table will no longer grow exponentially. Also, the frequent clearing causes the corresponding history table’s clustered index to get highly fragmented which significantly slows down the import.
Why disable history tracking?
Disabling this feature eliminates SQL transactions to the history table, which can improve the performance of insert, update, and delete operations on the user-created table.
Recommendations
Clear table operations
When a clear table operation is part of a nightly batch process, tracking history (including inserts, updates, and deletes) is typically unnecessary.
It is recommended to disable History Tracking on tables that are regularly cleared to prevent the corresponding history table from growing exponentially over time.
Index fragmentation
Frequent clearing of tables can cause the corresponding history table’s clustered index to become highly fragmented, which significantly slows down imports.
Disabling History Tracking helps avoid this issue, maintaining better performance.