Index defrag - rebuid/reorg

Question

Post reply

Index defrag - rebuid/reorg

Repriser-991084

Old Hand

Points: 396
More actions
November 10, 2009 at 4:28 am

#213765

Comments posted to this topic are about the item Index defrag - rebuid/reorg

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

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

Jim Dunn-363909 SSC Enthusiast Points: 107 More actions · Answer 1

Jim Dunn-363909

SSC Enthusiast

Points: 107

November 30, 2009 at 5:57 am

#1085357

Will this work for a sql2000 database?

Simon Facer SSCertifiable Points: 5427 More actions · Answer 2

DMV's are not in SQL 2000 - this won't work in that release.

Question - If you are rebuilding the Clustered Index, why do you then rebuild all the other indexes?

TechnoPeasant SSC-Addicted Points: 447 More actions · Answer 3

FROM http://www.mssqltips.com/tip.asp?tip=1791

1. Rebuilding the Clustered Index (CI) does not rebuild the Non-Clustered Indexes (NCI) unless the keyword ALL is specified, you will have do it separately.

2. If you rebuild your indexes online, you must ensure that you have enough available disk space to hold the index that is being created along with the pre-existing index (source and target indexes simultaneously). After the rebuild operation, the old index will be dropped though. Also, rebuilding indexes online takes a significant amount more time and resources than just rebuilding the index. This is usually a considerable tradeoff since the table will remain available during the rebuild operation.

3. The underlying table cannot be altered, truncated, or dropped while an online index operation is in process.

4. For partitioned indexes built on a partition scheme, you can use either of these methods (Reorganize and Rebuild) on a complete index or on a single partition of an index.

5. In general, fragmentation on small indexes is often not controllable, in other words rebuilding or reorganizing small indexes often does not reduce fragmentation. That is because the pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight different objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

6. Index rebuilding can be either online or offline.

7. Index reorganizing is always executed online.

8. The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

9. An index cannot be reorganized or rebuilt if the filegroup in which it resides is offline or set to read-only.

Madhu R SSC Veteran Points: 212 More actions · Answer 4

Hi,

Thanks for your script.

I run the script. But its didn't turned out any index need to be defrag or whatever.. !! First its told me the PROC is not available. And I changed as "CREATE" instead of "ALTER".

Please assist me.

JohnR Right there with Babe Points: 747 More actions · Answer 5

Is there a 'Read-only' version of this script that can tell us if ANY indexes really need a defrag or reorg? I think it would be better to try a read-only version before jumping into modifying any existing indexes.

jswong05 Hall of Fame Points: 3553 More actions · Answer 6

Read only. It is right there. I don't usually answer this kind of question. Today is exception.

Leave the line PRINT @sql, comment out exec @sql.

Jason
http://dbace.us
😛

jswong05 Hall of Fame Points: 3553 More actions · Answer 7

Madhu R (3/23/2010)
Hi,
Thanks for your script.
I run the script. But its didn't turned out any index need to be defrag or whatever.. !! First its told me the PROC is not available. And I changed as "CREATE" instead of "ALTER".
Please assist me.

Did you change these values?

@minFragPercent = 70,

@maxFragPercent = 100,

@minRowCount = 1000

Jason
http://dbace.us
😛