If you have performance issues on your database, one of the first things you should check is the fragmentation. If the fragmentation is high, SQL Server has the possibility to either reorganize or rebuild indexes. You can detect index fragmentation by using the Dynamic Management View (DMV) sys.dm_db_index_physical_stats and check the avg_fragmentation_in_percent column.
Use [MyDB]; SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id ORDER BY avg_fragmentation_in_percent DESC;
If the value avg_fragmentation_in_percent between 5% and 30% you should perform a reorganize of your indexes, not a total rebuild. A rebuild you only ne invoked if the fragmentation percentage is greater than 30%. No action should be taken if the fragmentation percentage is lower tha 5% which is a normal level of fragmentation.
The following command will rebuild all indexes With default fill factor :
Use [MyDB]; EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"