news.clarionlife.net

How To Reorganize All Indexes On MS SQL 2008

To transfer data, I made a backup of the database and just restored it to the new server. The speed of execution of queries has become much lower than on the old server.

I decided that it was necessary to rebuild all indexes in the database. You can do this using Management Studio. In this case, you will need to do this operation for each table. I have about 400 tables in the database. Therefore, it takes a long time.

I wrote a small script that performs these operations. I have updated statistics and clean the cache plans for queries at the end.

USE database_name

DECLARE @TableName varchar(255)
DECLARE @SqlQ      char(260)

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
  SET @SqlQ = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);'
  EXEC (@SqlQ)
  SET @SqlQ = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE'
  EXEC (@SqlQ)
  FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor

DEALLOCATE TableCursor
go

exec sp_msforeachtable 'update statistics ?'
go

dbcc freeproccache
go

Tags: MS SQL Server | No Comments »