Drop and recreate all indexes

Sql server 2005
There are a couple of administrators who have repeatedly said they
dropped and recreated indexes
and performance spiked after a migration and it would be nice to leave
no stone unturned in a bid to better performance.
Has anyone come across a script or has a way to do this
Your input as usual is greatly appreciated
MikeThere are many flavors out there... but this is a pretty common way to
reindex your tables.
The DBCC DBREINDEX statement is the key!
!UNTESTED SQL!
USE DatabaseName --Enter the name of the database you want to reindex
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161357089.891287.7040@.e3g2000cwe.googlegroups.com...
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Hi
Check out ALTER INDEX ALL in Books Online, example D in the topic
sys.dm_db_index_physical_stats shows you how you can call this for multiple
tables http://msdn2.microsoft.com/en-us/library/ms188917.aspx
John
"Massa Batheli" wrote:
> Sql server 2005
> There are a couple of administrators who have repeatedly said they
> dropped and recreated indexes
> and performance spiked after a migration and it would be nice to leave
> no stone unturned in a bid to better performance.
> Has anyone come across a script or has a way to do this
> Your input as usual is greatly appreciated
>
> Mike
>|||Thank you so much Immy.
Just ran something similar and the next step was to run a drop index
...
and recreate index ...
That is what is help is needed with ,again thank you for your time and
I appreciate more ideas|||Hi
DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
you are writing production code you should consider using ALTER INDEX.
John
"Massa Batheli" wrote:
> Thank you so much Immy.
> Just ran something similar and the next step was to run a drop index
> ...
> and recreate index ...
> That is what is help is needed with ,again thank you for your time and
> I appreciate more ideas
>|||As said earlier John the purpose is to completely drop and rebuild
indexes
Not sure why that has to be done but still looking for ways to do that
on instructions
Reason for this post
.....
John Bell wrote:
> Hi
> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
> you are writing production code you should consider using ALTER INDEX.
> John
> "Massa Batheli" wrote:
> > Thank you so much Immy.
> > Just ran something similar and the next step was to run a drop index
> > ...
> > and recreate index ...
> > That is what is help is needed with ,again thank you for your time and
> > I appreciate more ideas
> >
> >|||DBCC DBREINDEX and ALTER INDEX with the REBILD option will execute the same code internally as if
you do DROP INDEX and then CREATE INDEX.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1161806390.411989.59800@.i42g2000cwa.googlegroups.com...
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> .....
> John Bell wrote:
>> Hi
>> DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
>> you are writing production code you should consider using ALTER INDEX.
>> John
>> "Massa Batheli" wrote:
>> > Thank you so much Immy.
>> > Just ran something similar and the next step was to run a drop index
>> > ...
>> > and recreate index ...
>> > That is what is help is needed with ,again thank you for your time and
>> > I appreciate more ideas
>> >
>> >
>|||Hi
Rebuilding indexes should certainly be done post upgrading to SQL 2005, and
you should periodically rebuild your indexes to remove fragmentation to make
sure that will perform efficiently. You should also look at updating
statistics and usage. Check out the view sys.dm_db_index_physical_stats in
books online, which will give you an example script for rebuilding indexes if
they are fragmented by a certain amount.
John
"Massa Batheli" wrote:
> As said earlier John the purpose is to completely drop and rebuild
> indexes
> Not sure why that has to be done but still looking for ways to do that
> on instructions
> Reason for this post
>
> ......
> John Bell wrote:
> > Hi
> >
> > DBCC DBREINDEX may be removed in future versions of SQL Server, therefore if
> > you are writing production code you should consider using ALTER INDEX.
> >
> > John
> >
> > "Massa Batheli" wrote:
> >
> > > Thank you so much Immy.
> > > Just ran something similar and the next step was to run a drop index
> > > ...
> > > and recreate index ...
> > > That is what is help is needed with ,again thank you for your time and
> > > I appreciate more ideas
> > >
> > >
>