INDEX REBULID

Question

Post reply

INDEX REBULID

pradeep.k 50453

Ten Centuries

Points: 1380
More actions
May 17, 2013 at 5:23 am

#274025

HI,
ANY ONE GIVE ME SCRIPT FOR INDEX REBULID AND UPDATE STATISTICS IN SQL 2008 R2

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

Index rebuild itself update stats so, you do not have to update stats after index rebuild.

here is one script from web. But if you google it you will find many more., Choose the one which suits you best.

http://sqlfool.com/2009/03/automated-index-defrag-script/

But before implementing this... read through this..

http://msdn.microsoft.com/en-us/library/ms189858.aspx

pradeep.k 50453 Ten Centuries Points: 1380 More actions · Answer 2

pradeep.k 50453

Ten Centuries

Points: 1380

May 17, 2013 at 8:23 am

#1616205

thank you

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Another option for automating index and stats maintenance that I highly recommend:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jim Youmans-439383 SSCommitted Points: 1657 More actions · Answer 4

I agree with opc.three. This is one of the best scripts ever.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Glen Schwickerath SSC Enthusiast Points: 100 More actions · Answer 5

Glen Schwickerath

SSC Enthusiast

Points: 100

May 22, 2013 at 7:50 pm

#1617662

The Ola Hollengren scripts are hands down best.

pnauta Valued Member Points: 53 More actions · Answer 6

pnauta

Valued Member

Points: 53

May 23, 2013 at 2:41 am

#1617748

+1 for Ola Hallengren.

pradeep.k 50453 Ten Centuries Points: 1380 More actions · Answer 7

pradeep.k 50453

Ten Centuries

Points: 1380

May 23, 2013 at 2:45 am

#1617750

thanks for all updates

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 8

ReIndex all the Indexes from the Databases.

EXEC sp_msforeachtable @COMMAND1="PRINT '?' DBCC DBREINDEX('?','',80)"

GO

Update all Statistics from the Database.

EXEC SP_UPDATESTATS

GO

pnauta Valued Member Points: 53 More actions · Answer 9

The reason why Ola Hallengren's solution is better than the DBCC REINDEX commands:

- Microsoft recommends doing a reorganize only when fragmentation > 5% and < 30% and even do nothing when fragmentation < 5%, and then even ignore tables with < 10 000 rows.

- DBCC REINDEX syntax is deprecated (SQL 2000 syntax).

Ola Hallengren's script will dynamically choose what to do based on several factors:

- Licensing models (REBUILD ONLINE when possible)

- fragmentation level

- fields in tables (XML fields will prohibit REBUILD ONLINE and force REBUILD OFFLINE)

I have struggled for years with Microsoft Maintenance plans, and when this came along, I have started using it on all my db's. Whorks everytime, it's a no brainer and is even actively supported and updated.