Fragmentation information

Fragmentation occurs through data modifications (INSERT, UPDATE, and DELETE operations). For queries that scan part or all of a table, this fragmentation can cause additional pages to be read, adversely affecting performance.

Note: This topic applies only to on-premises environments.

You can use the Transact-SQL DBCC SHOWCONTIG statement to display fragmentation information for the data and indexes of a specified table.

To determine whether a table is heavily fragmented, use this command in SQL Server Management Studio, with the application database selected as the current database:

DBCC SHOWCONTIG (table_name)

In the result set, the value of Logical Scan Fragmentation gives an indication of the table's fragmentation level. The value should be close to zero, although a value from 0% through 10% might be acceptable.

To show in a grid an abbreviated result set for every index on every table, use this command:

DBCC SHOWCONTIG WITH TABLERESULTS, FAST

To show the full result set for every index on every table, use this command:

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

For more information, see SQL Server help for DBCC SHOWCONTIG.