Understanding Fragmentation and how it affects the performance of your database is critical. Performing regular maintenance tasks that defragment and compact clustered and non-clustered indexes on tables and views. This will improve index-scanning performance. This should be run on weekly or fortnightly basis. Fragmentation can happen at several levels and if fragmentation is allowed to occur, overall system performance suffers. Periodic defragmentation is necessary, but the following guidelines can help you minimize the number of time-consuming defragmentation processes.
File System Fragmentation
Disk blocks per database file should be kept contiguous on the physical platter within the NTFS file system. Fragmentation at this level can be prevented by pre-allocating all files to their expected size upon creation.
NTFS file system defragmentation tools should be avoided. These tools are designed to work at the operating system level and are not aware of internal SQL Server data file structures.
Extent Fragmentation
Within SQL Server, all of the pages within a file, regardless of table association, can become interleaved down to the extent size (2M) or page level (8K). This commonly occurs due to concurrent DML operations, excessive row-level updates, or excessive row-level deletes.
Fully rewriting the table(s) in question is the only way to ensure optimal page allocation within a file. There are no alternative methods to resolving this type of database fragmentation. For this reason, it is important to follow guidelines for SQL Server configuration and best practices for loading data and managing DML.
Index Fragmentation
Index fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and, therefore, to the indexes defined on the table. Because these modifications are not ordinarily distributed equally among the rows of the table and indexes, the fullness of each page can vary over time. For queries that scan part or all of the indexes of a table, this kind of fragmentation can cause additional page reads. This hinders parallel scanning of data.
An index can be in different physical (page) and logical (index) order.
Do not use the ALTER INDEX REORGANIZE command to resolve this type of fragmentation as this can negate the benefits of large allocations. An index rebuilds or an INSERT-SELECT into a new copy of the index (which avoids a resort) can resolve this issue. Any ALTER INDEX REBUILD process should specify SORT_IN_TEMPDB=TRUE to avoid fragmentation of the destination filegroup. MAXDOP 1 should also be used to maintain index page order and improve subsequent scan speeds.
Here are some recommendations to get you started. I highly recommend that you spend time on this area when starting to looking at DB performance.
- Use sys.dm_db_index_physical_stats to determine the extent of fragmentation.
- When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:
- Drop and re-create the clustered index. Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX.
- Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline.