If you are a more advanced with your database, or simply prefer more control over what is happening, then you can manually write T/SQL code that will rebuild or defragment your indexes. If you are using SQL Server 2008 or below, you can use the DBCC INDEXDEFRAG statement (this statement will be removed in SQL Server Kilimanjaro). Starting with SQL Server 2005 the recommended way (required starting in SQL Server Kilimanjaro) to defragment indexes is by using the ALTER INDEX statement with the REORGANIZE keyword.
ALTER INDEX ALL ON HumanResources.Employee REORGANIZE;
To rebuild your indexes you can use the older DBCC DBREINDEX statement (this statement will be removed in SQL Server Kilimanjaro). Starting with SQL Server 2005 you should use the ALTER INDEX statement with the REBUILD keyword to rebuild your indexes.
ALTER INDEX ALL ON HumanResources.Employee REBUILD WITH (FILLFACTOR = 75, ONLINE = ON);
You look at the level of fragmentation that your indexes have. The higher the level of fragmentation the slower statements against the indexes will run. Up through SQL Server 2008 you can use the DBCC SHOWCONTIG statement (like the other DBCC statements in this article, this statement will be removed in SQL Server Kilimanjaro). Starting in SQL Server 2005 you can use the sys.dm_db_index_physical_stats dynamic management function to see what the fragmentation ration of your indexes are.
SELECT * FROM sys.dm_db_index_physical_stats (db_id(YourDatabase), object_id(YourTable), NULL, NULL, NULL)
One of the columns returned from this statement is avg_fragmentation_in_percent. The lower the number the better.
There is no way to keep indexes from becoming fragmented. All that can be done is to keep it under control.
If your indexes are becoming fragmented too quickly then you need to decrease the interval that you defragment your indexes (run the job more often). If your indexes are still becoming too fragmented then consider rebuilding them and allocating more white space by lowering the fill factor percentage. If you are at 90% fill factor, and your indexes are becoming heavily fragmented after a couple of days, you may need to consider lowering your fill factor to 60-70%. While this will take up more disk space it will reduce the amount of fragmentation that occurs.
This article was first published on EnterpriseITPlanet.com.