SQL Server Index Fragmentation Overview

By: Brady Upton   |   Updated: 2016-06-22   |   Comments (5)   |   Related: > Fragmentation and Index Maintenance

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.

Let's INSERT 1000 rows into this table:

INSERT INTO Person VALUES
('Brady', 'Upton', '123 Main Street', 'TN', 55555)
GO 1000
        

Now, let's check our index again:

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.

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC
        

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.

Custom Script – Custom scripts are probably the way to go because you can apply logic to check index fragmentation and then decide on whether to skip the index, reorganize, or rebuild. I use Ola Hallengren's scripts and they do exactly what I need them to do.  MSSQLTips.com also has these resources - SQL Server Fragmentation and Index Maintenance Tips.

Third party software - Third party software will help defragment indexes so you don't have to.

Next Steps

• This tip gives you an idea of a few of the components involved in index fragmentation. MSSQLTips.com offers more comprehensive tips focused on each of the subjects I mentioned here.
• Make sure to check out Chad Boyd's SQL Server Fragmentation tutorial which offers great insight into numerous topics regarding fragmentation

About the author

Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips