November 9, 2011 at 3:34 am
Hello.
I don't know exactly the difference.
In a OLTP, if avg fragmentation>1, ¿rebuild or reorganize?
Suppose, that the table has 100000 pages.
Thanks for all.
November 9, 2011 at 3:40 am
It may be too much for you but I would recommend you to go through the article.
Reorganizing and Rebuilding Indexes
November 9, 2011 at 3:41 am
msimone (11/9/2011)
Hello.I don't know exactly the difference.
In a OLTP, if avg fragmentation>1, ¿rebuild or reorganize?
Suppose, that the table has 100000 pages.
Thanks for all.
In general
If Fragfragmentation <5% No Issues
If Fragfragmentation >5% AND <30% Re Organise
If Fragfragmentation>30% Re Build
November 9, 2011 at 5:12 am
Hakuna Matata (11/9/2011)
In general
If Fragfragmentation <5% No Issues
If Fragfragmentation >5% AND <30% Re Organise
If Fragfragmentation>30% Re Build
With the caveat that there need to be enough pages to make it possible/worth defragging. If the object only has 8 pages or less it's usually impossible to defrag because the object is small enough to be scattered around one or more "mixed extents".
November 9, 2011 at 7:04 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 7:20 am
Gail, the article is really educating but what’s the probability when Paul would say “It Depends”.
In most of the cases we can follow it as Rule of Thumb.
•if an index has less than 1000 pages and is in memory, don't bother removing fragmentation
•if the index has:
oless than 5% logical fragmentation, don't do anything
obetween 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)
omore than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)
November 9, 2011 at 7:27 am
As Paul said:
They're a good starting point to work from.
<snip>
They're not set in stone - they're a big generalization, and there are a ton of other factors that may affect your choice of threshold and fragmentation removal method (e.g. recovery model, high-availability technologies in use, log backup schedule, query workload, disk space, buffer pool memory, and so on). I wish Microsoft would update the old whitepaper on fragmentation - they keep promising me they'll get around to it.
In the meantime, take those numbers with a pinch of salt and don't treat them as absolute.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 7:31 am
Ya but what does he know???
He only coded the whole thing and been working for 20+ years in sql server. It's not like being a super mvp and MCM means anything nowadays :-D.
November 9, 2011 at 7:35 am
Ninja's_RGR'us (11/9/2011)
Ya but what does he know???
I'm resisting the urge to say something that would get me into trouble... 😀
It's not like being a super mvp and MCM means anything nowadays :-D.
Paul actually isn't an MCM. He's (only) an MCM instructor and exam author.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 7:40 am
p.s.
Dev @ +91 973 913 6683 (11/9/2011)
obetween 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)omore than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)
DBCC INDEXDEFRAG and DBCC DBREINDEX are deprecated, should not be used any longer and are only in the product for backward compatibility with SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 7:40 am
Paul actually isn't an MCM
He doesn't need to be an MCM to prove his mastery on the subject. He has it & he has proved it long back and it’s still undeniable.
November 9, 2011 at 7:41 am
GilaMonster (11/9/2011)
Ninja's_RGR'us (11/9/2011)
Ya but what does he know???I'm resisting the urge to say something that would get me into trouble... 😀
It's not like being a super mvp and MCM means anything nowadays :-D.
Paul actually isn't an MCM. He's (only) an MCM instructor and exam author.
You're right, authors don't know squat!
who said that 😀
So he's not an MCA either (assuming not MCM)? I need to find better guys to learn stuff! :hehe:
November 9, 2011 at 7:44 am
Ninja's_RGR'us (11/9/2011)
GilaMonster (11/9/2011)
Ninja's_RGR'us (11/9/2011)
Ya but what does he know???I'm resisting the urge to say something that would get me into trouble... 😀
It's not like being a super mvp and MCM means anything nowadays :-D.
Paul actually isn't an MCM. He's (only) an MCM instructor and exam author.
You're right, authors don't know squat!
who said that 😀
Jen (half of Midnight DBA)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 7:44 am
GilaMonster (11/9/2011)
p.s.Dev @ +91 973 913 6683 (11/9/2011)
obetween 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)omore than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)
DBCC INDEXDEFRAG and DBCC DBREINDEX are deprecated, should not be used any longer and are only in the product for backward compatibility with SQL 2000.
Agree... It was a blind copy paste. Thanks for pointing it out.
November 9, 2011 at 8:09 am
Off the topic, but I was searching for MVPs around the World and found 280 MVPs for SQL Server.
I noticed you (Gail) in the list. May I know who else contribute here?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply