database reindexing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

database reindexing

Author

Topic

vb_bv
Starting Member

24 Posts

Posted - 2002-04-16 : 23:16:06

hi, all, just had a question, I been told to do a database re-indexing process can make th sql server run more smooth, does anyone can tell how can I achieve that? many thanks!

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-17 : 02:30:43

If you have a maintenance plan, there is a step that can be added to reindex (I believe the tab is optimization).

Or you can do it manually with dbcc dbreindex.

-Chad

vb_bv
Starting Member

24 Posts

Posted - 2002-04-17 : 02:33:12

yes, I found a script to achieve that

here is a script.

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
PRINT "Reindexing " + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

that's it.

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2002-04-17 : 02:33:56

For Databases, Indexing is a art specially when you use it for performance tunning.
Just go through this page
http://www.sql-server-performance.com/optimizing_indexes.asp

and if you want more info about indexes, refer BOL

---------------------------
Sandesh - The Messanger

vb_bv
Starting Member

24 Posts

Posted - 2002-04-17 : 03:01:35

hi, all, sorry that I still have a bit problem about database indexing...

I found a option called "Auto Update Statistic" is turn on from my database configuration, does that means the database will do the re-indexing automatically, and run the above script manully just do nothing in this case?

thanks you.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-04-17 : 09:44:57

The auto-update of statistics will not re-index for you but as data changes over a period of time SQL Server will resample the data to make sure the statistics stay relevant. The rate of re-sampling depends on the size of the table and when a significant portion of the total table size has changed. Check BOL for more info.

Justin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources