Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index defrag - rebuid/reorg


Index defrag - rebuid/reorg

Author
Message
Repriser-991084
Repriser-991084
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 469
Comments posted to this topic are about the item Index defrag - rebuid/reorg
Jim Dunn-363909
Jim Dunn-363909
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 47
Will this work for a sql2000 database?
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1591 Visits: 724
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
TechnoPeasant
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 71
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
Madhu R
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 43
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.
JohnSQLServerCentral
JohnSQLServerCentral
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 101
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
jswong05
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 476
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
:-P
jswong05
jswong05
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 476
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
:-P
jswong05
jswong05
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 476
Index rebuild online is only available in Enterprise Edition
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Online does not mean it does not lock. It is "almost online".

Rule of thumb:
5%-30% fragmentation - use reorg
30%-100% fragmentation - use rebuild
you can run an Agent job with two steps.
reorg uses 8K page to rewrite index one page at a time with no rollback.
rebuild completely recreate the index and switch over.

I have a newer version that resets fillfactor along the way if fillfactor=0. It is on SQL Saturday #308 recording on my web site.

From SQL2012 later, index included LOB column is allowed rebuild online.

Jason
http://dbace.us
:-P
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9740 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search