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:
Avg Fragmentation in Percent Value |
Action to take |
> 5% and <= 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD |
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
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:
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:
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:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Términos de uso Privacidad Cookie Preference Center