SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

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 (http://blog.SQLAuthority.com)

About these ads

54 thoughts on “SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

  1. 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 (http://msdn.microsoft.com/en-us/library/ms188917.aspx) 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 create\drop idea looks the best option.

      • 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.

          • Interesting, so what’s the advice of this DEITY to remove fragmentation of a heap without turning it into a clustered index? I don’t see any other possibility than create/drop a clustered index. If this is his stance on the argument, then why didn’t MS think of a tool (e.g. a DBCC command) to degrafment heaps? And why doesn’t Paul reply for himself? Too high to get down into human’s affairs?

    • 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?

  2. 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

  3. 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]?

  4. 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!

  5. 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

  6. 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.

  7. 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?

  8. 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

  9. 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.

  10. 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?

  11. 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.

  12. Pingback: SQL SERVER – Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31 « SQL Server Journey with SQL Authority

  13. 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

  14. 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

  15. Pingback: SQL SERVER – Cleaning Up SQL Server Indexes – Defragmentation, Fillfactor – Video « SQL Server Journey with SQL Authority

  16. 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 ?

  17. 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

  18. 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”.

  19. 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

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  21. 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.

      • 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

  22. 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 ?

  23. 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.

  24. 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 !!!

  25. 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

  26. 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

  27. 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

  28. 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.

  29. 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

  30. 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

  31. 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;
    )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s