Applies to
Summary
The following guide provides an overview of SQL Server instance index rebuild and reorganizes operations within the SQL index maintenance application.
Description
SQL Server database index is a data structure created to improve the speed of data retrieval row operations from a table or a view. In other words, the B-tree keys structure of database indexes give more power to a SQL Server and allows quick and efficient discovering of a specific row or rows based on its key values.
By its ability to quickly locate and access data, indexes are used to prevent the time-consuming search of every row in a database table every time one is accessed.
Every time insert, update or delete operations are made to a database data, the SQL Server instance database engine automatically modifies the affected indexes. This causes the existing index pages to split to make space for new key values. These modifications over a time period can cause the index information to become scattered in the database thus forming an index fragmentation.
To be more precise, when the logical ordering of index pages doesn’t match its physical ordering, based on the index key value, then database index fragmentation emerges and greatly affects database performance.
Index fragmentation requires additional I/O to locate data to which the index points and this greatly degrades database query performance and in most harsh situations causing severe database slowness.
Rebuild and Reorganize SQL Server instance indexes
To remedy index fragmentation, two available operations can be performed ensuring smooth database query performance: index rebuild, and index reorganize.
Database index rebuild operation creates a fresh new structure for an index by firstly dropping it then recreating that index from scratch. This operation can be done online or offline depending on the type of index and database engine version.
Database index reorganizes operation simply reorders the index page by expelling the free and unused page space, i.e. index reorganize process only restructure the information on the page.
There are two different approaches when it comes to rebuilding and reorganizing indexes in SQL index maintenance software: manually or automatically (unattended).
Defragment SQL Server database indexes manually
To rebuild or reorganize indexes manually, head over to the Fragmentation tab. From this tab, select a SQL Server instance in the server explorer on the left and choose database(s) as a target of index operation. Check indexes from the index grid and click desired defragment operation from the ribbon. In this case Reorganize option is selected:
Once index reorganizing operation is completed, all selected indexes are successfully defragmented, evident by the fragmentation percentage indicator:
|
Quick tip: Database index reorganize operation is always executed online and doesn’t hold any locks on targeted indexes, I.e. those indexes can be used in time of operation |
When it comes to rebuilding database indexes, there are two different approaches since defragmented indexes could be rebuilt online or offline.
Online index rebuild operation will hold no locks on database objects containing those indexes during the operation execution time. Contrary, offline index rebuild holds locks on the database objects during the operation.
More in-depth information about the two mentioned index rebuildmethods can be found in the Online vs offline SQL Server index rebuild in SQL Server instance article.
To rebuild indexes online, select database(s) from the server explorer, check its indexes and click Rebuild online option from the ribbon bar:
Once index defragmentation rebuild process is finished, index fragmentation is not detected on the selected database:
Automating defragmentation process
In the SQL index defragmentation tool, there is an option to easily configure policies that will run based on a set schedule.
A thorough guide with detailed steps on how to achieve this can be found in the Create SQL Server index defragmentation policies article.
