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.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

via: http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.