Change Auditor 7.x SQL Maintenance Plan (4243132)

Over time index fragmentation may lead to some degraded database operations and inefficient use of space within the database. The Rebuild Index task rebuilds indexes to remove logical fragmentation and empty space, and also updates index statistics. Table column statistics can be updated by running the Update Statistics task to avoid bad query plans. It is important to schedule this task to run regularly for best results. Advanced users may prefer to monitor their database to decide when maintenance should be executed and in which capacity.

Detecting Fragmentation by running following query:

SELECT

    OBJECT_NAME(Stat.object_id)

,I.name

,Stat.index_type_desc

,Stat.avg_fragmentation_in_percent

       ,I.fill_factor

FROM sys.dm_db_index_physical_stats(DB_ID(N'ChangeAuditor'),null,NULL,NULL,NULL) AS Stat

JOIN sys.indexes AS I ON Stat.index_id = I.index_id AND Stat.object_id = I.object_id

The column to pay more attention is avg_fragmentation_in_precent. This tells the percent of logical fragmentation (out of order pages in the index)

Microsoft recommends the following:

ALTER INDEX REBUILD

Rebuilding an index first re-creates the index and then drops the existing one. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. It also updates statistics at the same time.

ALTER INDEX REORGANIZE

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates. Reorganize an index when the index is not heavily fragmented. It does not update statistics.

DROP AND CREATE INDEX

This option should only be used when there is hardly any space left on the disk or you cannot add additional space to the existing server. By dropping and recreating the index it will not require much additional space. The downside is that during whole index creation time, index will not be ready for the queries and it will slow down the query performance.

Reorganize or rebuild the database indexes:

Within SQL Management Studio go to Management | Maintenance Plans | Right click and choose Maintenance Plan Wizard

1. Click Next on the starting page
2. Give the maintenance plan a name and description
3. Schedule the task - Recommend weekly or at least monthly during a maintenance window. Click next
4. For the maintenance tasks select
    - Rebuild Index
    - Update Statistics
5. Click Next
6. Leave the task order as-is
7. Select your ChangeAuditor database. Under Advanced options enable "Keep index online while reindexing" and keep the remaining defaults. Click next
8. Select your ChangeAuditor database. Keep the remaining defaults otherwise. Click next
9. Choose whether you want to write a report to text file or email | Click next
10. Click Finish
11. Right click the new Maintenance Plan and choose Execute when ready

Note 1:

"Keeping index online while reindexing" option maintains the existing indexes while the new indexes are being built. This means there must be adequate disk-space to hold both indexes until the old ones are dropped following rebuilding of the new indexes.  At a minimum, there should be 120 percent the size of the current database free/available to ensure all indexes can be rebuilt within the database. See the following Microsoft article for additional information on disk space requirements and performance considerations:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver15

Note 2:

Many maintenance tasks, including reindexing or updating statistics, alter the database when they are executed. With that in mind, ensure a database backup is taken prior to any database maintenance. This will ensure the database can be restored if for any reason the subsequent database maintenance fails.

Note 3:

Certain online index operations are not available in all editions of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see the following Microsoft article:

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15

Note 4:

If you are using the Standard Edition of SQL server, do not have a maintenance window long enough to run the Rebuild Index task offline or have insufficient disk space, then you may need to consider alternative options. Use the "Reorganize Index" task followed by the "Update Statistics" task.

Note 5:

Advanced users may prefer to rebuild only specific indexes based on database fragmentation statistics using the ALTER INDEX command

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15

Additional information: