Rebuild indexes in table

Job details

Name:	Rebuild indexes in table
Platform:	Sqlserver
Category:	Maintenance
Premium package:	Maintenance package
Description:	Rebuilds fragmented indexes in all tables listed in the ‘table list’ parameter.
Long description:	Rebuilds fragmented indexes in all tables listed in ‘table list’ parameter.
Version:	1.3
Default schedule:	5 22 6 *
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.
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.
continue on next run	YES	If set to “YES“ (default) the rebuild indexes procedure will continue where it left off.
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.
table list		List of tables (separated by comma, in format [database].[schema].[table]) where all indexes will be rebuilt. The list can only include a maximum of 10 tables.
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 rebuild fragmented indexes in SQL Server or Azure SQL Managed Instance databases to improve performance by decreasing page read-ahead time and thereby reducing response times for index scans.
Why: This job is crucial because fragmented indexes can significantly impair database performance. Regular maintenance of indexes ensures that database operations remain efficient and response times are minimized, particularly for workloads involving large index scans.
Manual checking: You can check the fragmentation manually by using SQL Server Management Studio or querying the DMV sys.dm_db_index_physical_stats:
- bc. SELECT * FROM sys.dm_db_index_physical_stats(DB_ID, OBJECT_ID, NULL, NULL, ‘DETAILED’);

Job Configuration

“Component Query”: Filters applicable database instances, targeting SQL Server instances above version 2005 with specific properties regarding database engine, access privileges, and SQL edition. Ensures compatibility and appropriate context for index rebuilding operations.
“Default Schedule”: Configured to run periodically, ensuring regular maintenance without manual intervention. The default setup triggers the job at 6 AM on the fifth day of every month.
“Executions”: Includes procedures to actively manage and rebuild indexes based on specified parameters (e.g., fragmentation level, fill factor) and table lists provided by the user.
“Force Install”: Forces the installation of the job upon detection of appropriate conditions during the initial or update deployment phases, indicating the critical nature of this maintenance task.

Operational Details

Specifies that only indexes in ONLINE databases with multi-user access and read-write capabilities are considered.
Rebuilding can extend to both clustered and non-clustered indexes depending on config settings.
Allows control over operational timings, with an option to disable the job during typical working hours to prevent potential disruptions.

Dependency Management

Includes various dependencies on specific database objects like tables and stored procedures that handle the historical and logistical aspects of index rebuilds.
Deploys clean-up operations in case of failures, ensuring that partial or aborted tasks don’t leave residue that could affect database performance or future job runs.

Reporting and Analytics

Provides a detailed report template which logs historical rebuild operations including metrics like table count, index size before and after the rebuild, and execution times. Specifically, it aims to equip database administrators with timely data about:
- The number of indexes rebuilt.
- Comparisons of index sizes before and after rebuilds.
- Detailed lists of which tables and indexes were included.

Tables and Lists Managed by Job

Table/Presentation Title	Description
Rebuild job history	Captures statistical data regarding the rebuild operations for easy monitoring and future reference.
Table list	Lists all tables selected for index rebuilding in the scheduled task runs.
Index list	Provides details of specific indexes targeted in each operation, including type and uniqueness.
Rebuild index history	Logs detailed results of each rebuild operation, useful for auditing and troubleshooting purposes.

| Not rebuilt index history | Captures logs of indexes that were not rebuilt, including reasons and any associated errors, important for continuous improvement of index management strategies.

Overall, this dbWatch job is an essential component of database maintenance, focusing on ensuring optimal performance through regular management of index fragmentation.