March 10, 2010 at 9:40 am
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 me:blush:
Thanks and regards,
JMSM 😉
March 11, 2010 at 7:26 am
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[/url]
Ola Hallengren's Index Optimize script which is included in his SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization[/url] solution
As always, don't use a script without testing it and understanding what it is doing.
Jack Corbett
Consultant - Straight Path Solutions
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
March 12, 2010 at 10:54 am
Thanks and regards, Jack.
JMSM 😉
March 12, 2010 at 12:48 pm
Your welcome. I'm always happy to help out where and when I can.
Jack Corbett
Consultant - Straight Path Solutions
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
March 15, 2010 at 2:10 pm
Consider these Index Best practices:
March 15, 2010 at 7:15 pm
hemin.shah85 (3/15/2010)
http://bestpractices-sql.blogspot.com/%5B/quote%5D
I went there and I don't see anything that addresses index maintenance.
Jack Corbett
Consultant - Straight Path Solutions
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply