SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild or Reorganize Indexes


Rebuild or Reorganize Indexes

Author
Message
JMSM
JMSM
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 985
Hello,

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

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 meBlush
Thanks and regards,
JMSM ;-)
Attachments
Aux-IndexFragmentation.xlsx (91 views, 14.00 KB)
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70908 Visits: 14949
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
JMSM
JMSM
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 985
Thanks and regards, Jack.

JMSM ;-)
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70908 Visits: 14949
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
hemin.shah85
hemin.shah85
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 147
Consider these Index Best practices:

http://bestpractices-sql.blogspot.com/
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70908 Visits: 14949
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
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