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

Rebuild or Reorganize Indexes Expand / Collapse
Author
Message
Posted Wednesday, March 10, 2010 9:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:57 PM
Points: 186, Visits: 805
Hello,

I apreciate if anyone can check the .xlsx that i've anexed on this post to answer to my "stupid" question

The result that i get is from a query that i get here at sqlservercentral and that is very usefull to get all index fragmentation on an instance. The results that i put on it are only 2 of 96 indexes that appears in my list where fragmentation level is greater than 30%
My question is based on Rebuild or Reorganize Indexes.

Whats difference between them?
Does reorganize free all free space from pages?
Whats the impact?
How much space should i add to tempdb?
How can i control what is happening beside?
How much time should i spent when rebuilding the indexes that have a fragemntation >= 80%?
What should i must care about, level of fragmentation or Fragment count?
What is the best sintax to rebuild or reorganize?

Once more i hope that you can help me
Thanks and regards,
JMSM









  Post Attachments 
Aux-IndexFragmentation.xlsx (44 views, 14.86 KB)
Post #880356
Posted Thursday, March 11, 2010 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
From BOL (this article):

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.


Rebuilding an index, except in Enterprise Edition with ONLINE = ON, is a blocking operation. Meaning that the index and table are not avialable during the Rebuild. Rebuilding an index also causes the statistics for that index to be updated.

From the same BOL entry:

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.


Reorganizing is NOT a blocking operation, but it does consume resources. Statistics are NOT updated with a reorganization.

The typical recommendation is to reorganize indexes with 10-30% fragmentation and rebuild indexes with > 30% fragmentation. There are also rules of thumb about only doing maintenance on indexes with at least a certain number of pages. This will vary depending on who you talk to.

For more information you should read the BOL article linked above and then this one as well.

There are many existing scripts out there that do index maintenance. 2 I would recommend evaluating are:

Michelle Ufford's Index Defrag Script

Ola Hallengren's Index Optimize script which is included in his SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization solution

As always, don't use a script without testing it and understanding what it is doing.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #880992
Posted Friday, March 12, 2010 10:54 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:57 PM
Points: 186, Visits: 805
Thanks and regards, Jack.

JMSM
Post #881979
Posted Friday, March 12, 2010 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
Your welcome. I'm always happy to help out where and when I can.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #882074
Posted Monday, March 15, 2010 2:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 21, 2013 1:09 PM
Points: 13, Visits: 147
Consider these Index Best practices:

http://bestpractices-sql.blogspot.com/
Post #883329
Posted Monday, March 15, 2010 7:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
hemin.shah85 (3/15/2010)
Consider these Index Best practices:

http://bestpractices-sql.blogspot.com/


I went there and I don't see anything that addresses index maintenance.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #883479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse