Although SQL Server automatically maintains indexes after any INSERT, UPDATE, DELETE, or MERGE operation, some index maintenance activities on your databases may still be required, mostly due to index fragmentation. Fragmentation happens when the logical order of pages in an index does not match the physical order in the data file. Because fragmentation can affect the performance of some queries, you need to monitor the fragmentation level of your indexes and, if required, perform re-organize or rebuild operations on them.
It is also worth clarifying that fragmentation may affect only queries performing scans or range scans; queries performing index seeks may not be affected at all. The query optimizer does not consider fragmentation either, so the plans it produces will be the same whether you have high fragmentation or no fragmentation at all. That is, the query optimizer does not consider whether the pages in an index are in physical order or not. However, one of the inputs for the query optimizer is the number of pages used by a table or index, and this number of pages may increase when there is a lot of unused space.
You can use the sys.dm_db_index_physical_stats DMF to analyze the fragmentation level of your indexes, where you can query this information for a specific partition or index, or look at all the indexes on a table, database, or even the entire SQL Server instance. The following example will return fragmentation information for the Sales.SalesOrderDetail of the AdventureWorks2012 database:
select a.index_id, name, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages
from sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2012'), object_id('Sales.SalesOrderDetail'), NULL, NULL, NULL) as a
join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id
In my copy of AdventureWorks2012, I got the following output (not all the columns shown to fit the page):
Although the level of fragmentation considered a problem may vary and depend on your database and application, a best practice is to reorganize indexes with more than 10 percent and up to 30 percent fragmentation. An index rebuild operation could be more appropriate if you have fragmentation greater than 30 percent. Fragmentation 10 percent or less should not be considered a problem.
An index reorganization operation defragments the leaf level of clustered and nonclustered indexes and is always an online operation. While rebuilding an index, you can optionally use the ONLINE = ON clause to perform an online operation for most of the index rebuild operation (a very short phase at the beginning and the end of the operation will not allow concurrent user activity). Rebuilding an index drops and re-creates the index and removes fragmentation by compacting the index pages based on the specified or existing fill factor configuration. The fill factor is a value from 1 to 100 that specifies a percentage that indicates how full the leaf level of each index page must be during index creation or alteration.
To rebuild all the indexes on the SalesOrderDetail table, use the following statement:
ALTER INDEX ALL ON Sales.SalesOrderDetail REBUILD
Here is the fragmentation in my copy of AdventureWorks2012 after running the previous statement:
In case you need to reorganize the index, which is not the case here, you can use a command like this:
ALTER INDEX ALL ON Sales.SalesOrderDetail REORGANIZE
Fragmentation can also be removed from a heap by using the ALTER TABLE REBUILD statement. However, it could be an expensive operation because it causes all the nonclustered indexes to be rebuilt as the heap RIDs obviously change. Rebuilding an index also has an impact on statistics maintenance.