Rebuild indexes
Job details
| Name: | Rebuild indexes | |
| Platform: | Sqlserver | |
| Category: | Maintenance | |
| Premium package: | Maintenance package | |
| Description: | Rebuilds fragmented indexes in all databases. | |
| Long description: | The main effect of fragmentation is that it slows down page read-ahead throughput during index scans, which causes slower response times. If the query workload on a fragmented table or index involve scans, removing fragmentation will have effect. | |
| Version: | 2 | |
| Default schedule: | 0 * * * | |
| 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 | 30 | The minimum fragmentation of the indexes to be rebuild. |
| minimum index size | 1 | The minimum index size (in MB) for indexes to be rebuild. |
| maximum index size | 10000 | The maximum index size (in MB) for indexes to be rebuild. |
| fill factor | 90 | The fill factor value (%). FILLFACTOR option is not allowed when rebuilding a columnstore index and will be ignored. |
| 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). |
| rebuild clustered indexes | YES | Runs “ALTER INDEX … REBUILD …” on clustered indexes. |
| rebuild non clustered indexes | YES | Runs “ALTER INDEX … REBUILD …” on non clustered indexes. |
| max elap time | 180 | The maximum number of minutes before the dbWatch task stops rebuilding the remaining indexes. |
| ignore databases | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. | |
| continue on next run | YES | If set to “YES“ (default) the rebuild indexes procedure will continue where it left off. |
| rebuild large indexes | NO | If set to “YES“ (default “NO”), the procedure starts with rebuilding of one index (the smallest one) from the list of indexes that were not rebuild because of too large size (larger than the “maximum index size” parameter value). |
| sort in tempdb | ON | By setting the parameter value to “ON” you can direct the SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. |
| online | OFF | When set to “ON” allows to leave all indexes online and accessible while they are being rebuilt (runs ALTER INDEX … ON … REBUILD WITH (ONLINE=ON)). The online option for index rebuilds is only available in Enterprise edition. For other editions this parameter is ignored. |
Job Summary
- Purpose: The purpose of this job is to monitor, maintain, and improve database performance through rebuilding fragmented indexes according to specified parameters and conditions.
- Why: This job is essential for maintaining database performance and efficiency. Fragmented indexes can slow down query performance and lead to inefficient storage utilization. By regularly rebuilding these indexes based on fragmentation levels and other metrics, the job ensures optimal database response times and operational continuity.
- Manual checking: You can check the status of indexes manually using SQL Server Management Studio or by running SQL commands to assess index fragmentation levels and other statistics related to index health.
Key Procedures and Operations
- The job includes checking each index within all selected databases to assess whether they exceed the minimum fragmentation threshold and if so, determining whether they should be rebuilt based on their size and other parameters.
- Rebuilding operations are only carried out during allowed times (outside of specified working hours) unless differently specified by parameters.
- Only indexes that meet the criteria, such as minimum and maximum size and specific fragmentation levels, are processed.
- Large indexes which are too big to be rebuilt within the maximum elapsed time set, can be specifically targeted in subsequent runs if configured.
Parameters Explanation (Excluded Detailed Listing)
- General settings like data retention period, index size constraints, and fragmentation thresholds.
- Operational flags for rebuilding clustered and non-clustered indexes, managing job execution time, and deciding on index rebuild continuation on the next run.
- Customization settings include whether or not to use ‘tempdb’ for sorting during the operation, and the option to rebuild indexes online, available only in Enterprise editions.
Dependencies and Reporting
- Dependencies: The job relies on several supporting tables for historical data and operational flags to manage execution state and record histories of activities such as:
- dbw_tab_rebuild_indexes
Implementation Overview
- The job is executed through a stored procedure that dynamically determines which indexes need rebuilding based on the set parameters and system conditions (like server load, data size, and operational windows).
- Additional logic to ensure robust operation includes error handling during rebuilds, logging detailed historical data for monitoring progress and diagnosing issues, and options for handling index rebuilds in database environments with specific configurations (like read-only databases in Always-On setups).
Compatibility and Execution Notes
- The task is structured for environments running SQL Server versions later than 2005, specifically targeting instances with full database engine capabilities.
- It is designed to honor server-specific configurations and operational constraints while maintaining the integrity and performance of the database system.