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)
 Rebuild index script

Author  Topic 

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-04 : 11:27:24
I am trying to run DBCC reindex script for the whole tables in one DB. Can you take a look at the below script and let m eknow if everthing looks ok. Alos, When is the best time to run this script? I want to run it once a week.

USE DatabaseName

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

Kristen
Test

22859 Posts
Posted - 2007-01-04 : 12:02:36
1) I recommend you use INDEX DEFRAG on large tables (>10,000 pages)
2) You may want to look at using SHOWCONTIG as a basic for which tables are fragmented
3) Tara has a script in her Blog that does this sort of thing

"I want to run it once a week"

4) If your database is doing nothing over night (i.e. rather than running flat out 24x7 ) then I suggest run it every night. The more optimal the data the more throughput for the users.

Bit of a hit on TLog backups though - but unless that is an issue got for it!

5) INDEX DEFRAG will require you to do the Update Statistics separately (unlike DBREINDEX). However, we do ours separately anyway because we don't want to use the default sample size (IME it isn't as good as using a FULLSCAN)

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Rebuilding%20Indexes,DBCC%20CHECDB,Reindex,INDEXDEFRAG,Tara%20Blog%20Houskeeping%20Routines

Kristen
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-04 : 12:43:27
Ok. Aftert I ran the tara's INDEX DEFRAG script, I rerun DBCC showcontig.. and got the below result.. How can I analyze the below statistic.. is the table in a good shape?

DBCC SHOWCONTIG scanning 'ProductInventory' table...
Table: 'ProductInventory' (549576996); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 3.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 155.5
- Avg. Page Density (full).....................: 98.08%
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-04 : 12:56:22
Alos, do i have to update statistics after I run DBCC indexdefrag command? if so, how cn aI do that for the whole tables?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-01-04 : 13:34:47
My recollection is:

Scan Density -- 100% is best, 0% worst
Logical Scan Fragmentation - Lower is better
Extent Scan Fragmentation - Lower is better
Avg. Bytes free per page - Lower is better
Avg. Page density (full) - Higher is better

so you look good - taking into account that there are only 6 pages in the index. IME you won't get high Scan Density percentages until you have quite a few pages (we use REINDEX < 10,000 pages and DEFRAG >= 10,000 pages)

"do i have to update statistics"

DEFRAG does NOT update statistics, but REINDEX does.

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-01-04 : 13:35:50
"how cn aI do that for the whole tables"

Sorry, missed that bit: the default for UPDATE STATISTICS is to do all indexes on a given table - see BoL for more details

Kristen
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-04 : 14:39:58
thanks kristen,,
One more question, How can I get the number of pages in a table so that I can decide whether I nned to use INDEX DEFRAG or DBREINDEX?
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-04 : 14:41:33
oops! nevermind.. I got the answer.

DO you run update statistic script after DBCC INDEXDEFRAG?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-01-04 : 15:06:30
yes I do

Kristen
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-07 : 14:25:20
After I ran DBCC indexfreg/update statistic for the whole DB, the size of back up file got smaller.. is this normal? if so, why the size of backup got smaller after ran DBCC indexfreg/update statistic?
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-07 : 15:14:11
but problem is after i did DBCC indexdefreg, the size of log file got huge.. the db sixe is 8gb but the size of log file is now 16GB...

What should i do? i would like to shrink the isze of log file.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-01-08 : 02:59:21
"why the size of backup got smaller"

Probably because the pages are more densely packed, so fewer pages to back up.

Backup only backs up the used pages - so will be smaller than the physical MDF file

Kristen
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2007-01-08 : 18:24:53
that make a sense. thank you
Go to Top of Page

bhsmy
Starting Member

3 Posts
Posted - 2009-04-01 : 04:36:59
Hi there,

I create a new job that run at night for rebuild table index in SQL 2000 but the job failed.

i rebuild index follow the suggestion in this page http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx which give me the below script(same as jung1975):

USE DatabaseName

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

May i know how can i add email notification in this script so that i can get an email about which table rebuild failed rather than just get job failed error message? Another is, how can i get average fragmentation % on each table? I know that running DBCC SHOWCONTIG command provides several measurements.

I read an article and the article mentioned that :"If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index need to be reorganized, finally if index average fragmentation is greater than 30% index need rebuilt." How can i add those checking in the script?

I am new in dba task thus need yours advice.Thanks.
Go to Top of Page

bhsmy
Starting Member

3 Posts
Posted - 2009-04-01 : 04:37:00
Hi there,

I create a new job that run at night for rebuild table index in SQL 2000 but the job failed.

i rebuild index follow the suggestion in this page http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx which give me the below script(same as jung1975):

USE DatabaseName

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

May i know how can i add email notification in this script so that i can get an email about which table rebuild failed rather than just get job failed error message? Another is, how can i get average fragmentation % on each table? I know that running DBCC SHOWCONTIG command provides several measurements.

I read an article and the article mentioned that :"If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index need to be reorganized, finally if index average fragmentation is greater than 30% index need rebuilt." How can i add those checking in the script?

I am new in dba task thus need yours advice.Thanks.
Go to Top of Page

bhsmy
Starting Member

3 Posts
Posted - 2009-04-01 : 04:37:02
Hi there,

I create a new job that run at night for rebuild table index in SQL 2000 but the job failed.

i rebuild index follow the suggestion in this page http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx which give me the below script(same as jung1975):

USE DatabaseName

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

May i know how can i add email notification in this script so that i can get an email about which table rebuild failed rather than just get job failed error message? Another is, how can i get average fragmentation % on each table? I know that running DBCC SHOWCONTIG command provides several measurements.

I read an article and the article mentioned that :"If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index need to be reorganized, finally if index average fragmentation is greater than 30% index need rebuilt." How can i add those checking in the script?

I am new in dba task thus need yours advice.Thanks.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site