question

Newbie Bala avatar image
Newbie Bala asked

Logical Scan Fragmentation

Hi, What is the Logical Scan Fragmentation Limit need to be maintained for a table? If the limit exceeds 50 then are we need to REORGANISE or DROP AND REBUILD the Indexes?
fragmentation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
In an ideal world you would have 0% fragmentation - this can be achieved through good table and index design and then keeping that table/database readonly. However, a production database (with writes and reads) will get fragmentation of some sort or other, regardless of table and index design. You should aim to keep this as low as possible - anything round 10% is pretty decent IMO. Take a look at the index maintenance scripts from [Ola Hallengren][1] or [Michelle Ufford][2] which are smarter than the average script. They interrogate the index fragmentation details and decide upon the action to be taken according to thresholds you set. They also use ONLINE operations when possible to do their work. [1]: http://ola.hallengren.com [2]: http://sqlfool.com/2010/04/index-defrag-script-v4-0/
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
Yeah but the index information is not so usable pre 2k5 - anyway noone lives in the stone age any more ;o) 2k5 is nearing end of life.... when will 2k or even SQL 7 installations finally shuffle of the edge of the world?
2 Likes 2 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
'tis a shame from my point of view that they don't work with SQL2000... hence my post, above. ;-)
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Let's see... I last used SQL6.5 about 6 months ago. I last used SQL 7.0 a couple of days ago. I last installed SQL2000 earlier this month. That doesn't answer your question, does it? ;-)
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
@Thomas, I would refuse to touch SQL6.5 and SQL7.0 or charge a lot more. :)
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I don't have to touch anything less than 2k now... one of the results of last weekend's mahoosive overtime claim! :-)
0 Likes 0 ·
Show more comments
Cyborg avatar image
Cyborg answered
[Microsft suggest the following][2], If fragmentation > 5% and < = 30% ALTER INDEX REORGANIZE and for fragmentation above 30% ALTER INDEX REBUILD You need to ignore fragmentation for smaller tables, while checking the fragmentation you need to consider then size of the tables and count of its pages please refer articles from [Brent Ozar Article][4] & [SQLServerPedia][5] for details [1]: http://sqlserverpedia.com/blog/sql-server-bloggers/index-fragmentation-findings-part-2-size-matters/ [2]: http://technet.microsoft.com/en-us/library/ms189858.aspx [3]: http://sqlserverpedia.com/blog/sql-server-bloggers/index-fragmentation-findings-part-2-size-matters/ [4]: http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/ [5]: http://sqlserverpedia.com/blog/sql-server-bloggers/index-fragmentation-findings-part-2-size-matters/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
If you look at the scripts provided by [Michelle Ufford][1] or [Ola Hallengren][2], you'll see that their scripts generally work off lower numbers by default. I've put together a SQL2k version of these scripts, and have said if LogicalFragmenation is over 30, then do a full DBCC DBREINDEX, otherwise if > 10 then do DBCC INDEXDEFRAG & UPDATE STATISTICS. NOTE: Don't bother doing "UPDATE STATISTICS" after a DBReindex - it's done for you as part of the reindex process. [1]: http://www.sqlfool.com [2]: http://ola.hallengren.com
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
45 seconds faster..... and you read my mind.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@WilliamD - I'm sure we've had that conversation before!
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
+1 for UPDATE STATISTICS.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Should I have explicitly said to not update statistics when doing a DBReindex?
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Ah, hell with it... :-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.