Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index defrag - rebuid/reorg Expand / Collapse
Author
Message
Posted Tuesday, November 10, 2009 4:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 146, Visits: 455
Comments posted to this topic are about the item Index defrag - rebuid/reorg
Post #816399
Posted Monday, November 30, 2009 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 6:09 AM
Points: 5, Visits: 42
Will this work for a sql2000 database?
Post #826141
Posted Monday, November 30, 2009 5:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:40 AM
Points: 1,568, Visits: 666
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?



Post #826424
Posted Tuesday, February 9, 2010 10:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:14 AM
Points: 51, 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.



Post #862608
Posted Tuesday, March 23, 2010 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 24, 2013 6:56 PM
Points: 6, Visits: 42
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.
Post #888658
Posted Wednesday, July 7, 2010 7:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 4:41 PM
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.
Post #948534
Posted Thursday, July 8, 2010 10:28 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
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
Post #949434
Posted Thursday, September 30, 2010 8:23 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
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
Post #996374
Posted Friday, July 18, 2014 12:06 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
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
Post #1593913
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse