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 especialy 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
Tom Hundley
Elegant Software Solutions, LLC
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 especialy important if you are using guids as primary keys and don't move the clustered index to a more logically sequential column.