SQL Server Index Fragmentation Overview

By:   |   Comments (5)   |   Related: > Fragmentation and Index Maintenance


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.


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:

dbo.Person 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.

SQL Server Index Properties and Fragmentation Before Inserts

Let's INSERT 1000 rows into this table:

('Brady', 'Upton', '123 Main Street', 'TN', 55555)
GO 1000

Now, let's check our index again:

SQL Server Index Properties and Fragmentation After Inserts

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.

DBCC SHOWCONTIG command and output

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.

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

Querying sys.dm_db_index_physical_stats for Average Fragmentation

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Brady Upton 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

Comments For This Article

Thursday, January 23, 2020 - 11:39:37 AM - Immaculate Back To Top (83958)

Thanks for the article. Can we find out how soon a table gets fragmented? The reason to know this information is apart from periodically doing a rebuild of indexes, i wanted to know if the underlying logic in our application can be redesigned better. One method i can currently think of is to fragment an index, take a database snapshot and keep checking the fragmentation percentage to determine the rate at which this happens on a table. But is there a better way available? Thank you.

Friday, September 20, 2019 - 5:51:48 AM - reader Back To Top (82513)

you should change 0 to 1 at

"You can also see from the screenshot above that this table went from 0 pages to 4."

correct: "You can also see from the screenshot above that this table went from 1 pages to 4."

Wednesday, August 28, 2019 - 2:00:31 AM - mahesh Back To Top (82163)

i made simple query to get all fragmentation percentage in sql instance.

Exec sp_MSforeachdb 'USE ?; 'select sys.databases.name AS DBname, sys.tables.name As tablename, sys.tables.object_id, sys.dm_db_index_physical_stats.avg_fragmentation_in_percent, sys.dm_db_index_physical_stats.index_type_desc from sys.dm_db_index_physical_stats (DB_ID(N'?'), Null, Null, Null, 'SAMPLED')
JOIN sys.tables ON sys.tables.object_id=sys.dm_db_index_physical_stats.object_id
JOIN sys.databases ON sys.databases.database_id=sys.dm_db_index_physical_stats.database_id
where avg_fragmentation_in_percent >=10
order by DBname,avg_fragmentation_in_percent DESC

hope you got result.

Thursday, June 23, 2016 - 3:26:01 PM - Dana Baxter Back To Top (41750)

Great article! 

Wednesday, June 22, 2016 - 12:57:00 PM - Colin Allen Back To Top (41740)

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


get free sql tips
agree to terms