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!
17 comments to “Index fragmentation and why you should care”
It’s going to be end of mine day, except before finish I am reading this great piece
of writing to improve my experience.
I read this post completely about the resemblance of most
recent and preceding technologies, it’s amazing article.
My family all the time say that I am killing my time here
at web, however I know I am getting familiarity every day by reading thes good posts.
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.
This permitted several river casinos to flourish across
Illinois.
I’d like to find out more? I’d care to find out some additional information.
What’s up to every body, it’s my first visit of this web site; this weblog consists of amazing and really fine data for visitors.
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!
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!
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
Hello, after reading this amazing piece of writing i am
also happy to share my know-how here with friends.
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
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
Actually when someone doesn’t know then its up to other viewers
that they will help, so here it occurs.
This post is genuinely a nice one it assists new internet people, who are wishing in favor of
blogging.
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.