Reorganize indexes in table

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to reorganize indexes in specified tables to mitigate the effects of fragmentation.
• Why: This job is crucial because fragmentation can significantly slow down the read-ahead throughput during index scans, which affects response times adversely. By reorganizing the indexes, it restores the logical order of the leaf nodes, compacts the index pages according to existing fill factors, and enhances overall query response times.
• Manual checking: You can check the status of index organization manually by issuing SQL commands to inspect the fragmentation levels and properties of indexes on the database.

Job Description

The task aims to reorganize both clustered and non-clustered indexes on specific tables within an SQL database. It targets indexes where fragmentation exceeds a specified threshold, ensuring an optimized performance only during non-working hours unless configured otherwise.

Key Features

• The job selectively reorganizes indexes based on their current fragmentation level.
• It operates under minimal system resources and avoids peak business hours to minimize its impact on system performance.
• Reorganization is applied to a user-defined list of tables, and it includes detailed tracking and reporting of its operations and outcomes.

Technical Details

• SQL Commands:

create table dbw_reorganize_indexes_in_table(database_name varchar(255), sch_name varchar(255), obj_name varchar(255), idx_name varchar(255), avg_fr_pct int, page_count bigint, stat_date datetime, avg_fr_pct_new int, page_count_new bigint, stat_date_new datetime, counter bigint,status varchar(3))

create table dbw_not_reorganize_indexes_in_table(database_name varchar(255), sch_name varchar(255), obj_name varchar(255), idx_name varchar(255), avg_fr_pct int, page_count bigint, stat_date datetime, counter bigint,status varchar(3), type varchar(32), error varchar(1024) null)

create table dbw_reorganize_indexes_in_table_histr(tab_count bigint, idx_count bigint, idx_size_before_MB bigint, idx_size_after_MB bigint, stat_date datetime, end_date datetime, elap_sec bigint, last_tab_name varchar(512) null, tab_checked bigint null)

create table dbw_reorganize_table_list(tab_no int, tab_name varchar(255))

create table dbw_reorganize_table_index_list(db_name varchar(255), sch_name varchar(255), tab_name varchar(255), obj_id int, idx_id int, type varchar(32), is_unique int, idx_name varchar(128))

CREATE PROCEDURE dbw_proc_reorganize_indexes_in_table(@taskId int) AS BEGIN DECLARE @Database VARCHAR(255) DECLARE @obj_name VARCHAR(255) DECLARE @idx_name VARCHAR(255) DECLARE @idx_type int DECLARE @obj_id int DECLARE @idx_id int DECLARE @avg_fr_pct float(10) DECLARE @page_count bigint DECLARE @avg_fr_pct_new float(10) DECLARE @page_count_new bigint DECLARE @cmd NVARCHAR(1500) DECLARE @min_avg_fr_pct int DECLARE @db_count int...

* This set of SQL commands and procedures ensures that indexes in specified tables are periodically reorganized to maintain database performance.

Scheduling and Condition

• The job operates on a default schedule “once daily at 21:05″ but avoids reorganization during working hours (7 AM to 5 PM) unless overridden by a configuration change.
• The reorganization is only initiated if the database properties meet certain criteria (UserAccess = MULTI_USER, Updateability = READ_WRITE, IsInStandBy = 0), and it focuses on indexes with fragmentation above a determined minimum threshold.
• The job reports and tracks the progress, providing a historical view and immediate feedback on the reorganization process including any errors encountered.

This systematic approach ensures both effectiveness in operation and minimal disruption, aligning with best practices for database maintenance.