SQL Server Index Fragmentation Overview

Problem

We've all heard about database/index fragmentation (and if you haven't, continue
reading), but what is it? Is it an issue? How do I know if it resides in my database?
How do I fix it? These questions could be a tip all in itself, but I'll try to give
you an idea of each in this tip.

Solution

Without going into a lot of detail, SQL Server stores data on 8KB pages. When
we insert data into a table, SQL Server will allocate one page to store that data
unless the data inserted is more than 8KB in which it would span multiple pages.
Each page is assigned to one table. If we create 10 tables then we'll have 10 different
pages.

As you insert data into a table, the data will go to the transaction log file first. The
transaction log file is a sequential record meaning as you insert, update, and delete records
the log will record these transactions from start to finish. The data file on
the other hand is not
sequential. The log file will flush the data to the data file creating pages all
over the place.

Now that we have an idea of how data is stored, what does this have to do with
fragmentation?

There are two types of fragmentation: Internal Fragmentation and External Fragmentation.

SQL Server Internal Fragmentation

SQL Server Internal Fragmentation is caused by pages that have too much free space.
Let's pretend at the beginning of the day we have a table with 40 pages that are
100% full, but by the end of the day we have a table with 50 pages that are only
80% full because of various delete and insert statements throughout the day.
This causes an issue because now when we need to read from this table we have to
scan 50 pages instead of 40 which should may result in a decrease in performance.
Let's see a quick and dirty example.

Let's say I have the following table with a Primary Key and a non-clustered index on FirstName
and LastName:

I'll talk about ways to analyze fragmentation later in this tip, but for now
we can right click on the index, click Properties, and Fragmentation to see fragmentation
and page fullness. This is a brand new index so it's at 0% fragmentation.

You can see our index becomes 75% fragmented and the average percent of full
pages (page fullness) increases to 80%. This table is still so small that 75% fragmentation
would probably not cause any performance issues, but as the table increases in size
and page counts increase you may see performance degrade. You can also see from the
screenshot above that this table went from 0 pages to 4.

SQL Server External Fragmentation

External Fragmentation is caused by pages that are out of order. Let's
pretend at the beginning of the day we have a perfectly ordered table. During the
day we issue hundreds of update statements possibly leaving some empty space on
one page and trying to fit space into other pages. This means our storage has to
jump around to obtain the data needed instead of reading in one direction.

Analyzing SQL Server Fragmentation

So is fragmentation an issue? I believe it is. If you can store your entire database in memory or if your database
is read only then I wouldn't worry about it, but most of us don't have that luxury.
I've worked on thousands of servers and analyzing fragmentation levels are one of
the first things I look at. In fact, just by fixing fragmentation, I've saw up to
200% improvements in query performance.

Speaking of analyzing fragmentation levels you may be wondering how we can do
this. Well, there are a few ways

DBCC SHOWCONTIG  this feature is old and will be removed in future versions
of SQL Server, but if you're still using SQL Server 2000 or below, this will help. Instead
of writing about it, I'll point you
here or you can check out Chad Boyd's tip
here. Both are good resources.

sys.dm_db_index_physical_stats  Introduced in SQL Server 2005, this dynamic management
view (DMV) returns size and fragmentation information for the data and indexes of
the specified table or view.

This is probably the most widely used method of analyzing fragmentation. You
can see from the screenshot above that I have an index named IX_RPCust_1 on the
RPCust table that is 98.83% fragmented. You can see more information on this DMV
here
from Arshad Ali.

Third party software - Third party software will help identify fragmentation
so you don't have to. Greg Robidoux offers a good tip on Idera's SQL Defrag Manager
here.

Fixing SQL Server Fragmentation

Now that we have found fragmentation in the database, how do we fix it? Like
analyzing indexes, there are multiple ways.

SQL Server Maintenance plans  Maintenance plans are shipped with SQL
Server and are nice for some tasks. Index maintenance in my opinion is not one of
them. You can add a rebuild index task or reorganize index task into the maintenance
plan, but the problem is that you can't really apply any logic to the plan. Without
logic, the plan will rebuild/reorganize EVERY index. Some indexes may not
be fragmented so they don't need to be rebuilt. Some indexes may have minimum fragmentation
so they would be fine with a reorganize. There's no way to specify this in a maintenance
plan. In
SQL Server 2016 there are new options for managing fragmentation.

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I use Ola's solution as well but also use Contig for file system fragmentation. https://technet.microsoft.com/en-us/sysinternals/contig.aspx

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.