Index fragmentation and why you should care

If there is one thing that can easily boost your SQL Server performance and must be indexes.
Creating an index is a (pretty) easy proces but maintenance on a heavily used and big index can be real challenge, a challenge that is easily forgotten by some administrators. Not performing regular index maintenance can remove all the performance boosts you gained by creating indexes in the first place!

In this article I’ll discuss the impact index fragmentation and how you can avoid it running wild on your system.

So what is this dreaded index fragmentation I hear about?
Index fragmentation is a normal thing, it doesn’t have to be a bad thing, especially not in small tables with only a few pages. It does become a problem on large, frequently accessed, tables where it can have an significant negative impact on the performance of an SQL Server.

To quote Microsoft from the SQL Server 2000 Index Defragmentation Best Practices:

The workload performance increase realized in the small-scale environment ranged from 60 percent at the low level of fragmentation to more than 460 percent at the highest level of fragmentation. The workload performance increased realized for the large-scale environment ranged from 13 percent at the low fragmentation level to 40 percent at the medium fragmentation level.

Even though the impact of fragmentation mentioned above comes from an old best practice file it is still relevant today.

Not one, but two types of fragmentation.
Basicly there are two types of index fragmentation (we’re skipping extent fragmentation here) Internal and External (or Logical) fragmentation.

Internal fragmentation occurs when pages are less then fully used. When new data is inserted and it doesn’t fit in the remaining free space of the page it will be written to a new page or added to a page that has enough free space. As you can imagine tables with alot of updates will contribute to this type of index fragmentation. Using very large row sizes can also have an impact.

External fragmentation occurs when index pages with a logical ordering do not match the physical ordering of the pages, this creates page chains. This type of fragmentation can also be caused by inserts or deletes.

So how can we fix it?
Well pretty easy, create a index maintenance plan!
To keep index fragmentation to a minimum you should REORGANIZE or REBUILD your indexes in a regular schedule. Both options can be used with the ALTER INDEX command but have different functions.

ALTER INDEX … REORGANIZE should be used when index fragmentation is between 5 and 30% (Microsoft guidelines) and it basicly reorders your index to remove the white spaces and create a continous chain of pages.

ALTER INDEX … REBUILD creates a new index and drops the old one. Microsoft suggests using this option if your index fragmentation is greater then 30%.

Keep in mind that reorganizing or rebuilding indexes can be an intensive operation and can take a while to complete. Another rather nasty side effect is that transaction log backups will grow in size, so if you use Log Shipping keep this in mind!

Also you shouldn’t just reorganize or rebuild every index, performing index maintenance on very small indexes with just a few pages can take longer then the fragmentation slows you down! So as always, experiment!

56 comments to “Index fragmentation and why you should care”

You can leave a reply or Trackback this post.
  1. May I simply just say what a relief to discover
    somebody that actually knows what they’re talking about online.

    You certainly know how to bring a problem to light and make it important.
    A lot more people need to check this out and understand this side of the story.

    It’s surprising you’re not more popular given that
    you surely possess the gift.

  2. Great blog you have here but I was curious if you knew
    of any user discussion forums that cover the same topics talked about here?
    I’d really like to be a part of group where I can get
    opinions from other experienced individuals
    that share the same interest. If you have any suggestions, please let me know.

    Cheers!

  3. Wһat i don’t understоod is if truth be told how you’re no longer actually a lot more
    well-preferred than you might be now. Υߋu’rе very intelligent.
    You know therefore considerably in relation to this subϳect, made me for my part belіeve it from
    so many numerous angles. Itѕ like men andd women aren’t involved unless it’s one thing to accomplish
    with Lady gaցa! Your personal stuffs excellent.
    All the timme care for it up!

  4. Very well written & done my friend!
    I’ve just started blogging myself very recently and
    noticed that lot of bloggers simply rehash old content but add very little of benefit.
    It’s terrific to see a beneficial post of some actual value to me, as a reader.

    It’s going on the list of creteria I need tto emulate being a new blogger.
    Visitor engagement and content quality are king.
    Some goiod ideas; you havee absolutely got on myy list of writers to follow!

    Continue the good work!
    Well done,
    Doralynn

  5. NayaJahan.com is one of Pakistan’s most influential source of information for entertainment fraternity, we post content synopsis/reviews and ratings, covering all genres of showbiz, behind the scene and current affairs and more… Our slogan say’s it all – NayaJahan – SITARO KI DASTAAN

  6. I was curious if you ever thought of changing the
    structure of your website? Its very well written; I love what youve got to
    say. But maybe you could a little more in the
    way of content so people could connect with it better.
    Youve got an awful lot of text for only having one or 2 pictures.
    Maybe you could space it out better? //aimslot.com

  7. Students in specific are encouraged to submit abstracts a prize will be awarded to the finest student poster as decided by a panel of judges.

  8. Some 40-years later the state’s Lottery Commission was approved in 1973, with its very first tickets going
    on sale soon immediately after.

  9. This is a good tip particularly to those fresh to the blogosphere.
    Short but very precise information… Appreciate your sharing this one.
    A must read post!

  10. Excellent post. I was checking constantly this blog and I’m impressed!

    Very useful info specifically the last part :) I care for such info much.
    I was looking for this particular information for a long time.
    Thank you and best of luck.

  11. Typically, if a bettor believes the underdog not only
    will cover but they will win outright, bettors will bet the moneyline on the underdog which could net a larger payout.

  12. From here, we know that the odds on an even dollars bet
    (such as red/black, odd/even) are 47.37% (18/38) on an American wheel and 48.6% on a European wheel.

  13. Hiya, I am reallky glad I have fouind tis information. Nowadays bloggers
    publish only ablut gossips and net and this is really
    frustrating. A gopod website with exciting content,
    thjis is what I need. Thank you for keeping this website,
    I will bbe visiting it. Do you do newsletters?
    Can’t find it.

  14. Pretty element of content. Ι simply stumbleⅾ սpon your website and
    in accession capital to say that I get in fact loved account
    your blog posts. Anyway I’ll be subscrіЬing on your augment
    or even I succesѕ yοu get right of entry to pеrsistently fast.

    My blog – kode situs bokep

  15. Asking questions are in fact good thing if you are not understanding something entirely,
    however this piece of writing offers nice understanding yet.

  16. Hi mates, how is everything, and what you would like to say regarding this piece of writing, in my view its in fact amazing in support of
    me.

  17. Yesterday, while I was at work, my cousin stole my iPad
    and tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My iPad
    is now broken and she has 83 views. I know this is
    totally off topic but I had to share it with someone!

  18. Thanks foor your write-up. My spouse and i have continually observed that almost aall people are desirous
    to lose weight simply because wish to show
    up soim in addition to looking attractive. Having said that, they do not constanttly realize thazt there are additional benefits to losing
    weight as well. Docttors insist that obese people come
    across a variety of diseases that can be perectely attributed to their paricular excess weight.
    Thee good thing is that people who are overweight in addition tto suffering from
    numerous diseases are able to reduce the severity
    oof thei particular illnessees by losing weight.

    It’s possible to see a steady but noted improvement in health as soon as
    even a minor amount of weight-loss is reached.

  19. Thanks for the concepts you have shared here. In addition, I believe there are several factors that will
    keep your car insurance premium all the way down. One is, to
    consider buying augos that are in the good listing of
    car insurance corporations. Cars which might be expensive are more
    at risk of being lost. Aside from that insurance policies are
    also based on the value of the car, so the costlier it is, then higher thhe prfemium you pay.

  20. Right here is the perfect web site for everyone who wishes to find out about this
    topic. You realize a whole lot its almost hard to argue with
    you (not that I really will need to…HaHa). You
    definitely put a fresh spin on a subject that’s been written about for years.
    Wonderful stuff, just wonderful!

  21. I discovered more new things on this weight-loss issue.
    One articular issue is that good nutrition is highly vital any
    time dieting. A tremendous reducton in junk food, sugary
    meals, fried foods, sugary foods, beef, and whkte flour products might
    be necessary. Possessing wastes parasites, and pooisons may prevent aims for fat-loss.
    While certain drugs briefly silve the problem, thee awful side effects arre certainly not worth it,
    they usually never give moore than a momentary solution. It is just a known idea that 95% of
    fad diets fail. Thank you for sharing your thinking on this web site.

Write a Reply or Comment

Your email address will not be published.