Here is a script to rebuild all of your indexes on your database. I find that indexes are often ignored once an application moves to production. It’s a good idea to evaluate your usage patterns and setup automated maintenance jobs to proactively rebuild indexes before fragmentation has a chance to adversely effect performance. This is especially important if you are using Guids as primary keys and don’t move the clustered index to a more logically sequential column.
-- Show fragmentation for all tables EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')" --Rebuild all indexes (note this method locks the tables while the indexes are being rebuilt) USE [myDatabase] DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor
Hope this helps someone.