Q. What is Fragmentation? How to detect fragmentation and how to eliminate it?
A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types:
- Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance.
- External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation.
Index pages also maintain a logical order of pages inside the extent. Every index page is linked with previous and next page in the logical order of column data. However, because of Page Split, the pages turn into out-of-order pages. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page. This is called Logical Fragmentation.
Ideal non-fragmented pages are given below:
Statistics for table scan are as follows:
- Page read requests: 2
- Extent switches: 0
- Disk space used by table: 16 KB
- avg_fragmentation_in_percent: 0
- avg_page_space_used_in_percent: 100
Following are fragmented pages:
In this case, the statistics for table scan are as follows:
- Page read requests: 6
- Extent switches: 5
- Disk space used by table: 48 KB
- avg_fragmentation_in_percent > 80
- avg_page_space_used_in_percent: 33
How to detect Fragmentation: We can get both types of fragmentation using the DMV: sys.dm_db_index_physical_stats. For the screenshot given below, the query is as follows:
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
ORDERÂ BY avg_fragmentation_in_percent DESC
Along with other information, there are two important columns that for detecting fragmentation, which are as follows:
- avg_fragmentation_in_percent: This is a percentage value that represents external fragmentation. For a clustered table and leaf level of index pages, this is Logical fragmentation, while for heap, this is Extent fragmentation. The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.
- avg_page_space_used_in_percent: This is an average percentage use of pages that represents to internal fragmentation. Higher the value, the better it is. If this value is lower than 75%, some corrective action should be taken.
Reducing fragmentation:
- Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
- Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
- If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEX REORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
- If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.
- (Update: Please note this option is strongly NOT recommended)Drop and re-create the clustered index: Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX.
Reference: Pinal Dave (https://blog.sqlauthority.com)
77 Comments. Leave new
Hi Pinal,
Thanks, I understand the query, but my results give me lots of ‘null’ values for the object_name, which makes no sense. Any idea as to how to reslove this as many of the indexes really do de-fragmenting.
Thanks,
Taruna
Dear Pinal, kindly tell me is this feasible for sql server 2000 also
Regards
Uttam
Hi Pinal,
Thanks for this very important and useful article.
Please suggest your view on “Remove Fragmentation from the table which is having UniqueIdentifier column as a PrimaryKey”.
Hi Pinal,
Thank you for your valuable article.
We have set the fill factor 80 for a sharepoint database server (serverwide setting). But one index rebuild job is running offline everyweek in the same server for all the databases with fill factor 70. The database is performing slow due to high level of fragmentation. Is this can be a reason for the slowness, if yes what should be the ideal value of fill factor? It is SQL Server 2012 and sharepoint 2010. Please help us regarding this.
Rajeev
HI
when i execute the query given to check the fragmentation percent it is giving an error saying “Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ‘(‘.”
what can be done.
is it specifig to any version.
Am using SQL Server 2008 R2.
Please let me know asap.
I am getting the same error as well, but I thought it might be because I was trying to do it dynamicly with sp_msforeachdb
Hi, I am getting the same error in SQL Server 2005 and I resolved it:
/* Get the database ID, for example 12345 */
SELECT DB_ID(N’AdventureWorksLT’)
/* Use de database ID 12345 in the Pinal’s query */
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(12345, NULL, NULL, NULL , ‘SAMPLED’)
ORDER BY avg_fragmentation_in_percent DESC
hi,
i tried this query .
there is repeat in index name and from same table …
ex
T_AirportInformation PK_T_AirportInformation 93.08056872 1055 CLUSTERED INDEX
T_AirportInformation PK_T_AirportInformation 100 3 CLUSTERED INDEX
T_AirportInformation PK_T_AirportInformation 0 1 CLUSTERED INDEX
like this why table and same kind of index is repeated ?
hi,
‘(‘ delete and write again ‘(‘ it will be successfully
First off, this has been extremely useful. However I’m unclear about one issue that was asked above and I’m having the same problem.
Taruna wrote:
“Hi Pinal,
Thanks, I understand the query, but my results give me lots of ‘null’ values for the object_name, which makes no sense. Any idea as to how to reslove this as many of the indexes really do de-fragmenting.
Thanks,
Taruna”
What was the resolution to that question?
Thanks.
Hi Pinal,
I am agree with Mike Dowel.
“Your way of explaining is way way better than some of the blog authors I read.
No matter what you write and how you write… you are always going to be one of my personal favorite. ”
Just like I am following a course from a SQL Guru!
Thanks for all the articles !!!
Thank you again. Because B-Tree is the bases of SQL index I did not think a clustered index would matter.
I read that when a non unique index is created on a table, SQL server adds a another byte or so to make the index unique. Is this true?
Is it better to create a unique clustered index than a nonunique one ??
Thanks
How to find out the Rebuild o reorganize the index based on the fragmentation, when it is 10 to 30 % we reorganize and when it is more than 40% we must rebuild it. how do i set this criteria in the maintenance plan ? Can you please suggest me. to put maintenance plan to reorganize or re build indexes automatically
Is anyone else running into an issue with the rebuild statement not changing the level of fragmentation found? (MSSQL 2008 R2 SP2)
ALTER INDEX ALL ON User.table REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,) ;
I run this script before and after and get the same data:
declare @db_name varchar (256)
set @db_name = ‘dbname’
SELECT object_name (object_id, database_id),
object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment
FROM sys.dm_db_index_physical_stats(DB_ID(@db_name), NULL, NULL, NULL , NULL)
WHERE avg_fragmentation_in_percent > 10
and index_type_desc = ‘CLUSTERED INDEX’
ORDER BY ObjectID, IndexID
I found that when re-indexing small tables (ones that don’t have many rows) that have under a hundred pages the fragmentation was always out. From what I remember there seemed to be a relationship between the size of a single row, the number number of pages and the FILLFACTOR but I never did the math to be sure.
It seemed like to me if you had a table where there FILLFACTOR was set to 100 and there was only one row in the table and the row filled half a page then you would get a result telling you you had 50% fragmentation because it wanted each page to be 100% full because the fill factor was set to 100 but it can’t ever get that because it only has one rows so as the number of rows increases the fragmentation percentage should go down. If anyone knows if this is true I would be interested in knowing if I have got this right?
Also heaped tables don’t reindex.
I have some databases that I am supporting that are importing and doing a lot of manipulation on tables by creating new tables and then dropping them. Part of the maintenance setup by the people that had been maintaining the databases before me is a job to shrink re-index and shrink the databases.
My understanding at the moment is that re-indexing will fix table fragmentation but shrinking the database will cause file fragmentation for the databases .mdf and .ldf files, is that correct? The database files are also stored on hard drives that are running in a raid 5 so I am wondering if file fragmentation would really matter or make a difference?
If that is true is there any way to check the fragmentation level of the database files? and would taking the database offline and moving the files (from one drive to another) fix the problem or would I need to defrag the hard drive, with all the databases turned off or something? There are about 40 databases and they are about 600Gig in total so I would like to fix them one at a time if possible. This is on an internal system so I am able to take it off line for about 2-4 hours a night or all day on Sunday if I need to. It is running in MS SQL 2005.
Thanks James,
I just added a “must be this tall” to the where clause to exclude the smaller objects:
declare @db_name varchar (256)
set @db_name = ‘Some Database’
SELECT object_name (object_id, database_id) AS ‘Object’,
object_id AS ObjectID,
index_id AS IndexID,
index_type_desc,
avg_fragmentation_in_percent AS PercentFragment,
page_count,
CAST(([page_count]*8)/1024 AS DECIMAL(16,2)) AS ‘Size’
FROM sys.dm_db_index_physical_stats(DB_ID(@db_name), NULL, NULL, NULL , ‘LIMITED’)
WHERE avg_fragmentation_in_percent > 10
and page_count > 2780
Pinal,
One thing to be aware of with these items is that you may need to update statistics after running these commands for the queries to correctly make use of the indexes. I was using a script based on this to update the most fragemented indexes – but the queries continued to perform poorly.
I was using sp_executesql to rebuild/reorganize the indexes across databases. Annoyingly dynamically running sp_executesql to update statistics on the individual tables didn’t work within the same procedure.
Regards
Alex
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
please review this post for how to fix fragmentation of heap table.
Hi Pinal, i have created i script for this..please check this is it ok…
(
select sys.tables.name as TableName,sys.indexes.name as IndexName,sys.indexes.type_desc as IndexType,
sys.indexes.index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
from sys.tables
inner join sys.objects on sys.tables.object_id = sys.objects.object_id
inner join sys.indexes on sys.indexes.object_id=sys.tables.object_id
inner join sys.dm_db_index_physical_stats(DB_ID(N’AdventureWorksLT’), NULL, NULL, NULL , ‘SAMPLED’)
on sys.indexes.index_id=sys.dm_db_index_physical_stats.index_id
where sys.dm_db_index_physical_stats.index_id in (1,2)
order by avg_fragmentation_in_percent desc;
)
Hi Pinal,
While detecting fragmentation, I found the need of ALTER INDEX REORGANIZE. But my db is under mirroring. What all steps I need to take before i go for INDEX REORGANIZE?
Hi pinaldave sir,
you are the library of SQL server, i like your smart answers.
The query needs a slight change to it. I was running the query and was not getting any information for the Object_Name call.
It may be the version of management studio I am running SQL 2012, but I had to modify and add the database_id to call.
From this
OBJECT_NAME(OBJECT_ID)
To this
OBJECT_NAME(OBJECT_ID, Database_ID)