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 INDEX … REORGANIZE“ on clustered indexes. |
| reorganize non clustered indexes | YES | Runs “ALTER INDEX … REORGANIZE“ 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
- Purpose: The purpose of this job is to reorganize fragmented indexes in all databases to optimize database performance and ensure efficient data retrieval.
- Why: This job is important to check and rectify fragmentation in the indexes of databases on the MS SQL Server platform. Once the fragmentation thresholds are reached, reorganizing the indexes can prevent slower response times during data retrieval, thus maintaining the performance and efficiency of the database operations.
- Manual checking: You can check for index fragmentation manually in the database by issuing the following SQL commands:
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:
- Selects fragmented indexes based on specified fragmentation thresholds (minimum and maximum).
- Physically reorders the leaf-level pages of indexed data.
- Compacts index pages based on current fill factor settings to optimize space usage.
- Checks only ONLINE databases with properties set to UserAccess = MULTI_USER, Updateability = READ_WRITE, and IsInStandBy = 0.
Exclusion Settings:
- Excludes specific databases and tables based on configurations in the job parameters.
- Allows disabling the job during working hours to avoid performance overhead during peak times.
Performance Considerations:
- Conditions are set to focus on defragmentation of indexes above a minimum size and below a maximum size, if not configured otherwise by ignoring index size parameters.
- Capabilities to handle large indexes based on parameters and can be opted to continue the left-off process in next run cycle, which ensures all indexes are eventually optimized.
Error Handling:
- Records indexes that could not be reorganized due to various constraints (size, time limits, errors) into separate history tables.
- Adjusts index processing based on the dynamic constraints of page-level locking.
System Resource Usage:
- Designed to use minimal resources during the execution of the reorganization process.
- Operates under a set maximum elapsed time per run, ensuring the system does not get overloaded.
| 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
- Includes error handling mechanisms to log issues during reorganization.
- Supports database configurations that enable or disable job features according to organizational requirements, such as disabling during work hours or continuing reorganization in subsequent runs.
- Offers a detailed logging and reporting framework that tracks and reports each run’s progress and issues.