Reorganize indexes


Job details

Name: Reorganize indexes
Platform: Sqlserver
Category: Maintenance
Premium package: Maintenance package
Description: Reorganizes fragmented indexes in all databases.
Long description: Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.
Version: 2.64
Default schedule: 15 22 4 *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’]/.[eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
keep data for 180 Number of days to provide statistics for.
minimum fragmentation 10 The minimum fragmentation of the indexes to be reorganized.
minimum index size 1 The minimum index size (in MB) for indexes to be reorganized.
maximum index size 10000 The maximum index size (in MB) for indexes to be reorganized.
maximum fragmentation 30 The maximum fragmentation of the indexes to be reorganized.
disable during working hours YES This parameter disables the alert to be scheduled, or to be run manually, during working hours (between 7am and 5pm).
reorganize clustered indexes YES Runs “ALTER INDEXREORGANIZE“ on clustered indexes.
reorganize non clustered indexes YES Runs “ALTER INDEXREORGANIZE“ on non clustered indexes.
max elap time 180 The maximum number of minutes before the dbWatch task stops reorganizing the remaining indexes.
ignore databases master List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. System databases tempdb and model er excluded.
continue on next run YES If set to “YES“ (default) the reorganize indexes procedure will continue where it left off.
reorganize large indexes NO If set to “YES“ (default “NO”), the procedure starts with reorganizing of one index (the smallest one) from the list of indexes that were not reorganized because of too large size (larger than the “maximum index size” parameter value).
enable page level locking NO Enables page level locking before reorganizing the index. When set to “YES“ the index will be altered with ALLOW_PAGE_LOCKS = ON. After reorganizing the index is altered with ALLOW_PAGE_LOCKS = OFF.
REBUILD when page level locking is OFF NO If set to YES the task will run rebuild index instead of running reorganize index if the page level locking for the index is off. The parameter “enable page level locking” must then be set to NO.
ignore index size parameters NO If set to “YES“ the Task ignores all parameters related to index size, and consequently reorganizing all indexes.
ignore tables List of tables (separated by comma, in format [database].[schema].[table]) which will be ignored. The list can only include a maximum of 10 tables.

Job Summary

SELECT OBJECT_NAME(ind.OBJECT_ID) AS ObjectName, 
    ind.name AS IndexName, indexstats.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.indexes AS ind 
    ON ind.object_id = indexstats.object_id
    AND ind.index_id = indexstats.index_id
    WHERE indexstats.avg_fragmentation_in_percent > 10 ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Job Execution

This monitoring job for dbWatch Control Center involves several key operations focused on database maintenance:

Index Reorganization Process:

Exclusion Settings:

Performance Considerations:

Error Handling:

System Resource Usage:

Feature Usage
ONLINE / OFFLINE Index Processing ONLINE only
User Access Level Required MULTI_USER
Updateability of Database READ_WRITE
Exclusion of stand-by databases Yes (IsInStandBy = 0)
Resource Appreciation Minimal during reorganization
Scheduled Timing Configurable but generally outside working hours if set

Extended Capabilities

Notes:
This job configuration provides a balanced approach to maintaining database performance through index management by reorganizing fragmented indexes while respecting system availability and operational schedules.