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
Don’t ever suggest create/drop clustered index for heap fragmentation removal, or drop/create of a clustered index for clustered index fragmentation removal. That’s almost the worst advice you can give. Both will cause all non-clustered indexes to be removed twice.
Don’t ever suggest drop/recreate nonclustered index either – it allows any constraints being enforced to be broken.
Rebuild or reorganize, using the thresholds I put in Books Online, or whatever works for you.
I’m sure you’ll edit the article to remove the bad advice.
Thanks
Can I ask Why is this a bad thing? According to the Microsoft Website the recommended way of reducing Heap fragmentation is to create and drop a clustered index.
I have been given the task of looking after a SQL 2005 database that has, on average, 90% fragmentation across almost every index and there is not a clustered index in sight.
I need to be able to sort out the fragmentation without changing the structure and the createdrop idea looks the best option.
Don’t ever follow “Don’t ever” advise.
Boys, do your selves a favor and slow down a bit. Paul Randal is like a minor deity in MS SQL. He works for Microsoft’s SQL development group and wrote portions of the product. I’ve personally seen his name in system stored procedures.
Just boys? Can the women go ahead and rush on in?.
first of all, i am a big fan of yours.
have a question though. what if its truly a heap, meaning no clustered index and no non clustered index. in that case it should be okay to create/drop the clustered index right? this would get rid of the fragments of space and make the data nice and tidy and ordered?
Hi , Pinal
Will it be fine if cluster index has fragmentation between 5 to 30% then apply ALTER INDEX REORGANIZE ?
Or need to drop and recreate cluster index for same?
Can you please explain me for “Fragmentation of Cluster index ” and it’s solution?
Thanks in advance.
Hello Paresh,
ALTER INDEX REORGANIZE is best method to defrag an index and we should use this method as first corrective action. But if this does not perform the full defragmentation then we should use ALTER INDEX REBUILD method.
Thanks,
Pinal Dave
Pinal,
I’m a bit confused because you’ve removed the bad advice Paul was referring to from your post. But doesn’t rebuilding an index using (Alter Index with Rebuild) Drop and recreate it again? or does Alter with rebuild work different from Drop Index [Index Name] then create Index [Index Name] ?
I stumbled upon a discussion about fragmentation and (N)VARCHAR versus (N)CHAR data types. Consensus was that when the length of the field is no more than 8 use CHAR and above that use VARCHAR (or NCHAR and NVARCHAR). These conclusions were backed up by tests but I don’t know excatly how tests were executed and what their results were.
So I would like to ask from more knowledgable here, is there any actual difference between padded fixed length CHAR and non-padded variable length VARCHAR considering fragmentation and performance or is this just prematual optimization which is usually a Bad Thing[tm]?
Pinal,
This is the best SQL Article I ever read and I totally loved it.
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 awesome!
One of the best!
Thanks!
Hi Pinal,
Thanks for the query, nice article.
Paul,
You had mentioned it is not good to drop and recreate instead use Rebuild or reorganize, using the thresholds I put in Books Online, or whatever works for you.
Could you provide more detailed explanation this issue.
Thank you
Using the information in the query, could the ALTER INDEX command be put together. Or is more information required?
Hi Pinal Sir,
It is very easy to read what you write. I have sent you few questions in email.
Nayan
Good one. Never knew it.
Very interesting thread.
Honestly I do not see anything wrong in what Pinal suggested. He had suggested three methods to reduce the fragementation and that was the answer.
He never really compared one over other. You can say article was not complete but he is not incorrect.
I think what Paul suggested is very correct too.
Overall as I said very interesting.
Uh. I just got the job to redesing our index maintenance. I discovered that our indexes are over 90% fragmented and obviously our old maintenance procedure is not working correctly.
There’s a problem though. Indexes need rebuilding badly but I was told that I can’t do that because the DB is under constant changes. I should only reorganize them. I suggested using index rebuild with ONLINE option but it was rejected by saying “it can lead to deadlocks” even though it has been never tested with this DB.
I would like to have yours opinion about this. Could rebuilding lead to problems even with ONLINE option?
And how about if I make it differently. I copy/create all the fragmented existing indexes exactly as they are but just with new names. When that is done I drop all the original indexes. Would this work?
Hello Marko,
Rebuild index with ONLINE option does not block other processes. So this is the better way instead of creating new indexes and then deleting old indexes.
Regards,
Pinal Dave
hello pinal,
can i know about Rebuild index with online option in detail…Functionality
Hi,
I have SQL Server 2000 version and I have a table with Clustered and 4 non-clusters Indexes.
I’m running the DBCC cmd :
DBCC SHOWCONTIG WITH ALL_INDEXES, TABLERESULTS
Column ScanDensity is having values between 50 and 95. So I have decided to perform DBCC DBREINDEX on all the indexes (cluster and 4 non-cluster)
but I’m little confuse what will be the sequence of DBREINDEX mean on which index I should perform DBREINDEX first and so on and what will the impact if I first Reindex cluster index and then reindex non-cluster.
SQL Server 2000 didn’t having option to disable index.
Please suggest.
Hi,
Very very useful blog…Thank you Pinaldave..!
Hi Pinal,
Thats really a very useful information.
I’ am trying to rebuild indexes online in one of our pre production environment. While doing an analysis of the index sizes , i observe that size of a clustered index on one table is 80G. The table has got around 100 million records.Is that usual? Is there a way to reduce the size of the index in sql server?
I am using a shared server, don’t know how some one hack into my mysql and made changes, i had changed the admin user and also password, but it is not helping. At last i have to change my server, now the hacking attacks stopped. Is it important to have a dedicated server to stop this kind of attacks.
Hi Guys,
Pls how do i know findout what Index has been affected from the (no Column Name).
example DF_Billofmaterilas_statdate
so i can use the right statement
Alter index …….. on ………………….
rebuild
Hi Pinal
Is it recommended to create a maintenance plan for Index Rebuilding? What are the pros and cons?
I am planning to create the plan, so it will be a great help for me, if i could get the answer.
Thanks a lot
Vandana
Hello Pinal Dave
i would like to know the use of “FILLFACTOR option” at indexes
Hi can you explain me something ?
I have de table with 45 millions reccords in a SQL2012 DB no primary key and no clustered index (not made by me ;) )
All my index are fragmented at ~99% and when i run this query :
ALTER INDEX ALL ON [Table_Name] REBUILD PARTITION = ALL
My fragmentation is at 0% great
But at this point the sqlserver process start using 15% to 20% of CPU and accessing the disk non stop during 2 or 3 houres and when it stop, the fragmentation is again at ~99%
I am the only one who use the DB…
should I kill someone or do magical incantation ? What is the problem of my database ?