Database Fragmentation

Hi Naomi

A couple of thoughts.

Are you shrinking the db at any point? - I believe that leads to substantial fragmentation. Best not to do it at all.

According to MS best practices, fragmentation is pretty much irrelevant for small indexen, because there’s no actual loss in performance from being fragmented –scanning a handful of pages is so fast anyway, improvement wouldn’t be noticeable.. Our LP_MAINTAIN_INDEXES script, which we run nightly, and which chooses whether to re-org, rebuild, or ignore indexes based on size and fragmentation state, ignores any index with less than 500 pages – I have seen suggestions that anything less than 1000 pages isn’t worth looking at. So those indexes are always showing up as heavily fragmented for us – probably not relevant for you, but if it’s mostly small indexen showing up as fragmented, it’s definitely not something to worry about.

There are quite a few tables in Tess that are HEAPS – that is, they have no clustered index. The table itself shows up in the index report as an index with type = HEAP, even though there’s no index as such – sort of a non-indexed index. They will almost certainly show as having a high fragmentation percentage, and they can’t be defragmented as they are - there’s nothing you can do about that, short of maybe adding a clustered index, rebuilding it, then deleting the clustered index again – I don’t think that’s recommended, or even helpful. But they’re not a problem, I believe.

Ken McSwain Business solutions Manager

kmcswain@sydneyoperahouse.com

T+61 2 9250 7876  F+61 2 9251 7821  M 0418 659 360

SYDNEY OPERA HOUSE BENNELONG POINT

GPO BOX 4274, SYDNEY NSW 2001, AUSTRALIA

SYDNEYOPERAHOUSE.COM

Tessi DB Geeks,

I am running into an issue where no matter what I do, rebuild, reorganize I have a huge amount of fragmentation in the database. I have 148 indexes that range anywhere from 98% fragmented to 10.25% the latter isn't too bad but I try to keep it under 10%. I have ran the UP_Rebuild_All_Indexes stored proc to no avail, used SSIS to rebuild and reorganize, I have developed t-sql to rebuild and reorganize, I have used the built-in SQL maintenance but no luck,  any other ideas?

I'm not having a performance problem but I am try to be proactive rather than reactive.

Naomi

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi Naomi

A couple of thoughts.

Are you shrinking the db at any point? - I believe that leads to substantial fragmentation. Best not to do it at all.

According to MS best practices, fragmentation is pretty much irrelevant for small indexen, because there’s no actual loss in performance from being fragmented –scanning a handful of pages is so fast anyway, improvement wouldn’t be noticeable.. Our LP_MAINTAIN_INDEXES script, which we run nightly, and which chooses whether to re-org, rebuild, or ignore indexes based on size and fragmentation state, ignores any index with less than 500 pages – I have seen suggestions that anything less than 1000 pages isn’t worth looking at. So those indexes are always showing up as heavily fragmented for us – probably not relevant for you, but if it’s mostly small indexen showing up as fragmented, it’s definitely not something to worry about.

There are quite a few tables in Tess that are HEAPS – that is, they have no clustered index. The table itself shows up in the index report as an index with type = HEAP, even though there’s no index as such – sort of a non-indexed index. They will almost certainly show as having a high fragmentation percentage, and they can’t be defragmented as they are - there’s nothing you can do about that, short of maybe adding a clustered index, rebuilding it, then deleting the clustered index again – I don’t think that’s recommended, or even helpful. But they’re not a problem, I believe.

Ken McSwain Business solutions Manager

kmcswain@sydneyoperahouse.com

T+61 2 9250 7876  F+61 2 9251 7821  M 0418 659 360

SYDNEY OPERA HOUSE BENNELONG POINT

GPO BOX 4274, SYDNEY NSW 2001, AUSTRALIA

SYDNEYOPERAHOUSE.COM

Tessi DB Geeks,

I am running into an issue where no matter what I do, rebuild, reorganize I have a huge amount of fragmentation in the database. I have 148 indexes that range anywhere from 98% fragmented to 10.25% the latter isn't too bad but I try to keep it under 10%. I have ran the UP_Rebuild_All_Indexes stored proc to no avail, used SSIS to rebuild and reorganize, I have developed t-sql to rebuild and reorganize, I have used the built-in SQL maintenance but no luck,  any other ideas?

I'm not having a performance problem but I am try to be proactive rather than reactive.

Naomi

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!