SQL Server Fragmentation Demystified Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

What is SQL Server Database Fragmentation? There are two major types of database fragmentation. One type of fragmentation is logical disk fragmentation and is a function of the operating system. As data is modified, added, or removed fragmentation occurs when the OS is unable to create the file in single, logically contiguous space. This fragmentation forces the disk head to move back and forth across the disk when reading a single file and slowing access.

The other major type of fragmentation is SQL Server fragmentation. This occurs, regardless of logical fragmentation, when the data stored inside the database files index is fragmented. Index fragmentation is categorized as either internal or external. Fragmented indexes can degrade query performance. Depending on workload characteristics, this heavy fragmentation can severely affect access rate.

Logical Disk Fragmentation

Just like any other operating system files, SQL Server database files can be logically fragmented on the disk slowing access for data manipulation. The defragmentation tools to assess fragmentation and defragging included in Windows can be used to remove this type of fragmentation. Unfortunately, SQL Server must be closed during the defragmentation process. If SQL server is open, even if not being accessed by a user, the database files are in "use" and cannot be defragged. There are third party defragmenters that are able to circumvent this problem to some extent.

To minimize fragmentation in the database files, avoid using the auto grow setting in SQL Server. For example, if SQL Server creates a 100MB file in contiguous space and auto-grow operations increase the size, the added space will not be contiguous and may fragment as the file grows. Allocate enough size for a file at the beginning and create files on already defragmented, contiguous space. In addition, when growth is needed increase the size in a large increment. Finally, keep the database files on separate disks so logical contiguous space is available.

SQL Server Index Fragmentation

Usually a more serious source of fragmentation in SQL Server is that which occurs in the SQL Server Index. In this case, the database files are fragmented at the index level and is unrelated to logical disk fragmentation. Fragmented index data can force the SQL Server to perform unnecessary data reads and page switching and query performance of a fragmented table can be very poor. Fragmentation of the index is defined as internal or external. External fragmentation is quite different from logical disk fragmentation.

Internal fragmentation

When pages have a lot of free space, most often resulting from rows removed by DELETE statements, they are described as internally fragmented. Other causes are page splits that are not filled to at least half. This empty space on pages means fewer rows per page and causes more page reads to access database rows. When systems are read-intensive, these extra reads can result in degradation in SQL Server performance.

External fragmentation

Ideally, when tables grow from insertion of new data, pages are allocated to an extent (a collection of contiguous blocks in a file). When that extent is filled, a new extent is then filled up with pages and so on. In this way, pages will always be located adjacent to each other and reading eight pages might require reading only one extent from disk. Unfortunately, in some cases this not possible and fragmentation results.

Viewing Fragmentation Information

With SQL Server 2000, the DBCC SHOWCONTIG function provides a measure of the extent of fragmentation in a SQL Server index. It can provide either the information for all indexes on a table or a single index. DBCC SHOWCONTIG, by default, scans the page chain from leaf to leaf, but it is also possible to scan all of the levels of an index. The data of importance is the Logical Scan Fragmentation that provides, as a percentage, the ratio of pages that are out of logical order. Anything over 10 % is an indication of significant external fragmentation.

Although DBCC SHOWCONTIG remains a viable option to determine the fragmentation in newer versions of SQL Server, a dynamic management function (DMF) is used provided in SQL Server 2005 and 2008 for determining the index fragmentation level. This new DMF (sys.dm_db_index_physical_stats) function accepts parameters to specify the database, database table, and index to be evaluated.

Several options also allow choice of the level of detail desired for index fragmentation. Although the sys.dm_db_index_physical_stats DMV statement provides index statistics far beyond the information provided by DBCC SHOWCONTIG, the key column in this case is avg. fragmentation in percent, a value between 5-30% indicates you need to reorganize the index. When the value is greater than 30%, it will be necessary to rebuild the index.


In SQL Server 2000, you must use the DBCC DBEREINDEX or DBCC INDEXDEFRAG statements to rebuild or defragment indexes. In SQL 2005 and 2008, the ALTER INDEX statement is used for these functions. The ALTER INDEX statement provides modification of an existing table or view index (relational or XML). Modification includes disabling, rebuilding, or reorganizing the index in addition to alteration of index setting options.

Reorganization of an index entails a physical reordering of the pages at leaf-level to match the logical, that is, left to right order of the leaf nodes. Based on the existing fill factor value, the index pages are also compacted. In SQL Server 2000, the ALTER INDEX statement in not available and you must use the DBCC INDEXDEFRAG statement instead.

Rebuilding an index will efficiently reduce fragmentation. The procedure automatically drops and re-creates the index while removing fragmentation. The procedure also reclaims disk space and reorders the index rows so they are on contiguous pages. Again, in SQL Server 2000, since ALTER INDEX is unavailable you must use the DBCC DBREINDEX instead.


In large databases where frequent data manipulation is common, index defragmentation is especially important. Usually a database manager includes automated processes in SQL Server maintenance plans to prevent performance degradation resulting from index fragmentation. In SQL Server 2000 Maintenance Plans, rebuilding the index is the only option. However, with SQL Server 2005 and 2008 you can perform either an index rebuild or an index defragmentation. For logical disk fragmentation, set up an auto determination of fragmentation and then arrange the defragmentation with the least disruption of the system.


What is SQL Server defragmentation? How does it happen? How can it be measured? How can it be minimized? How can it be fixed? Is it important?

The answers to these questions are no mystery. Depending on the version of SQL Server in use, the procedures to determine and correct defragmentation are not terribly complicated and in newer versions downright simple. In addition, they can be accomplished without unnecessary disruption of the system.