We are happy to announce that ApexSQL Defrag 2018 R3 has been released. In this version, several new features have been added, along with many usability and quality improvements. Significant improvements have been applied to defragmentation policies, reports and schedules.
Index fill factor management
Indexes can now be rebuilt with the custom, user specified fill factor value. All new indexes are created with the default fill factor value set to 100 (which is also equivalent to 0). This means that each leaf-level page of an index is 100% filled with data. This scenario is optimal for indexes on the static tables that do not have frequent insert or update operations written. If the table rows get updated much often, it is recommended to modify the fill factor value in order to decrease the number of page splits, and thus increase the performance on that table.
In ApexSQL Defrag, fill factor value for each index is displayed in the grid in fragmentation tab. To change the fill factor of an index, check its row in fragmentation grid, and click on the option Set fill factor from the context menu. In opened form, new fill factor value can be specified, as well as the rebuild type that will be used for the operation.
All policies now use improved scheduler. Older versions of the application were limited to either single run, or daily schedules. New scheduler offers way more in terms of flexibility – schedule frequency can be set to single run, daily, weekly or monthly. Additionally, start and expiration date can be set for each schedule.
Proactive resource check
Server resources can now be checked for CPU load, available RAM and hard disk memory, active jobs, and active transactions count on demand. This is particularly useful when new defragmentation policies are defined, and resource thresholds need to be set for the policy.
The resource check is run from the Thresholds tab of the Policy wizard. Just check the boxes in front of the resource labels, specify the thresholds values (or leave them at zero), and hit the Check button. Values for the available server resources will be obtained and presented as table in a new form. If indexes targeted by the policy are located on multiple servers, resource check will be run on all those servers. Final grid presents values set for each threshold in the first row, compared with checked resources on all servers containing indexes that are specified as policy targets.
Advanced policy settings
Advanced tab of the Policy wizard now offers the possibilities:
- To specify the operation sequence for each policy. Indexes targeted by the policy can be sorted and processed ascending or descending relative to fragmentation or size.
- To limit number of processor cores that are used for the policy execution. SQL Server supports using up to 64 processor cores for the defragmentation jobs. If this value is set to 0, all available CPU cores will be used for the policy (this is also default SQL Server setting).
- To delay policy execution on locked server objects for the specified time in minutes. If the value is set to 0, there will be no delay, and the operation will fail for the locked object.
Custom T-SQL scripts
Custom T-SQL scripts can be pre-loaded and set to run before or after the policy execution. The option is available in T-SQL tab of the Policy wizard.
Pause, resume or cancel index defragmentation
All operations that are available in the Fragmentation tab can now be paused, resumed or canceled on demand. This includes fast and deep analyze, index reorganize, as well as index online and offline rebuild operations. The feature is particularly useful when large indexes are processed.
CSV and XML format exports
All available reports can now be exported to CSV and XML formats.
Report on registered servers, databases, tables and indexes
Report that lists all server objects registered in ApexSQL Defrag is now available. As all other reports, it can be exported to PDF, IMG, HTML, CSV and XML format.
July 5, 2018