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!

123 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.

  22. Thanks , I have recently been looking for information about this topic
    for a long time and yours is the greatest I have discovered till
    now. However, what in regards to the conclusion?
    Are you certain concerning the source?

  23. Derived from the concepts of emblematic interactionism,
    partnership based training exploits the patterns of interaction, interpretation and correction between canines as well as their trainers.
    Property on a positive partnership between all of them, the
    technique lays out to achieve results that help both the pet dog and also the personal trainer, while at the same
    time enriching and enhancing their partnership.

    A relationship-based approach to pet training is actually certainly not
    essentially reliant on utilizing specific training aids
    or even handles yet posits that the hookup between pet dog
    as well as fitness instructor is sufficiently highly effective to achieve the instruction goals.
    Dunbar founded Sirius Dog Training, the very first off-leash instruction plan specifically for new
    puppies, which focuses on the importance of training
    bite inhibition, sociality, and various other general home manners, to dogs under 6 months of age.

    Canines perform not train themselves, when left to choose their behavior they are going to imitate dogs.

    It was actually found that the functionality opportunities for
    completion of the job were actually comparable for pet dogs trained along with either operant conditioning or the
    design rival method. The looking glass approach ideology is
    actually that dogs naturally discover by following the example of others in their social realm.

    In the 1980s vet and also creature behaviorist Ian Dunbar found that regardless of proof on the peak knowing time periods in pets, handful of pet instructors
    dealt with new puppies before they were six months outdated.

    Dunbar has created several publications, as well as is actually known for his worldwide workshop presentations as well as acclaimed online videos on puppy
    dog and dog actions and also training. The more you show and also manage your young
    puppy, the much less opportunity it are going to must take part in poor habits.

    Use these instruction jobs as you combine the puppy dog right into your
    life. For instance, ask your new puppy to “sit” before getting her food
    items, “sit” before you let her in or even out the door,
    and “rest” just before you pet her.

    Our grown-up degree pet instruction courses can easily help your pet develop reside a happy and healthy lifestyle.
    Petco pet dog training experts will certainly bolster positive actions and build upon simple hints.

    Clicker training is a nickname given to a certain form of good
    support training system, based on operant conditioning, that utilizes a clicker device
    as a marker and also bridge. Center to the program is actually
    consisting of the dog with all facets of the owner’s lifespan as well as favorable encouragement of duplicating habits.

    Secondary reinforcers are actually points the animal being actually
    taught delights in yet that are not needed forever, including toys, approval,
    and so on. There are actually an assortment of well established procedures of creatures training, each with its own followers as well as critics.
    The typical attributes of successful methods
    are recognizing the pet’s characteristics and character, accurate timing of support or penalty and
    consistent communication.

    Based upon the principles of social discovering, model-rival training utilizes a
    style, or even a competitor for interest, to illustrate
    the desired actions. Puppy dogs start learning from rise and
    great dog breeders start dealing with as well as socialization immediately.

    These are opportunities when your puppy dog desires something and
    also is actually most likely to conform. In this
    way, you are educating your dog at all times, throughout
    the time and additionally setting up expected guidelines as well as schedules for
    interactions and also helping the canine to know who handles the resources.

    The amount of time you devote qualifying your new puppy now will definitely repay when you possess
    a grown-up pet., How one can (Do) Dog Training Virtually
    Immediately, Derived from the concepts of emblematic interactionism, partnership based training exploits the patterns of interaction, interpretation and correction between canines as well as their trainers.

  24. Thank you, I’ve just been searching for info about this subject
    for a long time and yours is the best I have found out so far.
    But, what concerning the bottom line? Are you certain in regards to the source?

  25. okwin11 สปินฟรี สล็อต888 ผู้ให้บริการคาสิโนออนไลน์ทุกรูปเเบบ สล็อตเว็บตรง คาสิโนเว็บตรง เกมยิงปลา รูเล็ต เสือมังกร slot888 ลองรับทุกแพลตฟอร์มบน PC
    เเละ Android ios ที่มีเเอพฟังเคชั่นทุกระบบทำให้ลูกค้าสะดวกสบายมากขึ้นเล่นได้ทุกที่ทุกเวลา ฝากถอนด้วยระบบอัตโนมัติรวดเร็วทำรายการเพียง 30 วิเท่านั้น สล็อตxo168 ด้วยค่ายเกมมากมายกว่า 30 ค่ายเกมดัง PG Red Tiger SEXY BACCARAT SAGAMING JOKERSLOT QTech Games เเละอีกมากมาย บริการตลอด 24 ชม.
    คอยดูเเลทั้งวัน ตลอดเวลา สปินฟรี สล็อตเว็บตรงไม่ผ่านเอเย่นต์

    ซื้อฟรีสปิน

  26. A motivating discussion is definitely worth comment.
    I think that you ought to write more about this
    topic, it might not be a taboo matter but generally folks don’t speak about such issues.
    To the next! Kind regards!!

  27. I do not know whether it’s just me or if everyone else
    experiencing problems with your website. It looks like some of the
    written text in your posts are running off the screen. Can someone else please provide feedback and let me know if this is happening to them as
    well? This might be a issue with my web browser because I’ve had
    this happen previously. Thanks

  28. Whats up are using WordPress for your site platform?

    I’m new to the blog world but I’m trying to get started and create my own. Do you need any html coding knowledge to make your
    own blog? Any help would be greatly appreciated!

  29. I don’t even know how I finished up right here,
    but I thought this publish was great. I don’t know who you are however certainly you’re going to a famous blogger should you aren’t already.

    Cheers!

  30. magnificent publish, very informative. I’m wondering why the other experts of this sector do not understand this.
    You should continue your writing. I’m sure,
    you’ve a great readers’ base already!

  31. You could certainly see your skills within the article you
    write. The arena hopes for more passionate writers like you who aren’t afraid to say how they believe.
    Always go after your heart.

  32. What’s Happening i am new to this, I stumbled upon this I’ve found It positively
    useful and it has helped me out loads. I’m hoping to give a contribution & help other users
    like its aided me. Good job.

  33. Please let me know if you’re looking for a author for your blog.
    You have some really great articles and I believe I would
    be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some articles for
    your blog in exchange for a link back to mine. Please blast
    me an e-mail if interested. Kudos!

  34. excellent publish, very informative. I wonder why the other experts of this sector
    don’t notice this. You must continue your writing. I’m confident, you’ve a huge readers’ base already!

  35. Hello everyone, it’s my first pay a visit at this web site, and paragraph
    is actually fruitful in favor of me, keep up posting such articles.

  36. Thanks , I have just been searching for info about this subject for a long
    time and yours is the greatest I have found out till now.
    However, what about the conclusion? Are you positive about
    the source?

Write a Reply or Comment

Your email address will not be published.