Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bobbins
Starting Member
3 Posts |
 Posted - 2008-10-27 : 13:47:22
|
| Smaller tables seem to be ignored by DBREINDEX leaving high levels of logical fragmentation untouched (eg 83%).Is this normal? Anyone know why it happens?Is there a way to recognise indices where you shouldn't even try to reindexed regardless of their logical fragmentation? (e.g. < certain number of rows). I have this on various small tables up to with a few tens of thousands of rows. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-10-27 : 14:06:32
|
| It is not possible to defragment small tables. Yes this is normal, these tables should be ignored.I actually opened a case with Microsoft to find out at what point we should ignore indexes on small tables. The response was that at about 1000 pages or less, the index can not be defragmented. You can not make a decision based upon number of rows, but rather it has to be number of pages.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-27 : 14:07:02
|
| Are those table Heap? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bobbins
Starting Member
3 Posts |
Posted - 2008-10-29 : 14:56:59
|
| Thanks Tara, much appreciated... Microsoft's answer of "about 1000 pages or less" seems a little vague, I expect there's a bit more science behind it than that...! :)Any idea why they don't defrag at less than 1000 pages? I can only assume that it would make no difference, which seems strange. And even if it doesn't make a difference, it would still be nice to defrag them just out of OCD ;)A while back I had a corrupted index on a reference table with only 8 rows which (randomly) caused queries run against it to hang... I dropped and recreated the primary key and everything was fine after that. I know that's nothing to do with fragmentation, but it may explain why I'm unusually wary of indices on small tables! |
|
|
|
Bobbins
Starting Member
3 Posts |
Posted - 2008-11-27 : 07:13:38
|
| Anyone know if the limit is actually 1000 pages, and why there is a limit? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 09:59:55
|
| Because Row sizes/pages are small and it performs great without using index. You shouldn't worry fragmentation if it performing good for small tables. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
DBREINDEX does nothing for smaller tables?