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!