When you perform any data modification operations (INSERT, UPDATE, or DELETE statements)
table fragmentation can occur. When changes are made to the data that affect the
index, index fragmentation can occur and the information in the index can get scattered
in the database. Fragmented data can cause SQL Server to perform unnecessary data
reads, so a queries performance against a heavy fragmented table can be very poor.
If you want to determine the level of fragmentation, you can use the DBCC SHOWCONTIG
statement. The DBCC SHOWCONTIG statement displays fragmentation information for
the data and indexes of the specified table or view.