Here is another script to reindex all tables with fill factor 90%:

USE pmdb$primavera --your database name might be different

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

No Problem!

Hi David,

You can run this script to produce the script that will rebuild all indexes :

set head off pagesize 0 echo off verify off feedback off
spool reb.out
select ’alter session set sort_area_size = 36000000;’
from dual
/
select ’alter index ’ || index_name || ’ rebuild;’
from user_indexes
/
spool off
set head on pagesize 35 verify on feedback on
@@reb.out

Thanks to Guri Singh
Sr. Oracle DBA

Eric.

Have you tried rebuilding your indexes.
I run this script on my database regulary. It keeps it really fast.

Here it is :
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN (’master’,’model’,’msdb’,’tempdb’,’distrbution’, ’mmdb$primavera’)
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ’DECLARE TableCursor CURSOR FOR SELECT table_catalog + ’’.’’ + table_schema + ’’.’’ + table_name as tableName
FROM ’ + @Database + ’.INFORMATION_SCHEMA.TABLES WHERE table_type = ’’BASE TABLE’’’

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL 2000 command
--DBCC DBREINDEX(@Table,’ ’,@fillfactor)

-- SQL 2005 command
SET @cmd = ’ALTER INDEX ALL ON ’ + @Table + ’ REBUILD WITH (FILLFACTOR = ’ + CONVERT(VARCHAR(3),@fillfactor) + ’)’
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


All this will do is rebuild all indexes for all tables in your database.

Hope this helps.

Regards,
Arend Kok