locked
SQL Server 2012 Enterprise, Index fragment & page density issue RRS feed

  • Question

  • Hi guys,

    We have an index maintenance issue on large tables recently.

    Basically, there is a job running to do the index maintenance daily: do the index scan via sys.dm_db_index_physical_stats, and then reorganize indexes with fragment % between 10 and 30.

    We find some large tables (default fill factor is 100%) with quite low pay density, avg_page_space_used_in_percent = 18.91039041, which is expected to have a high index fragment % for reorganization to increase the pay density. However, the index fragment avg_fragmentation_in_percent is 7.130605409%, which means NO need for reorganization.

    Total size = 128.12 GB; Actual data size =128.12 *18.91039041%= 24.23 GB; Free size = 128.12 GB- 24.23 GB= 124 GB

    I then do a manual reorganization on this index. The fragment percent avg_fragmentation_in_percent is 1.423850519%, and page density avg_page_space_used_in_percent is 98.49016555%.

    Total size = 24.62 GB; Actual data size =24.62 * 98.49016555%= 24.25 GB; Free size = 24.62 GB- 24.25 GB =0 GB

    Based on our business, we delete data in this large table periodically, which should lead to low page density. But according to the result, we do need an reorganization to save disk space and optimize memory/IO usage. Why the index fragment doesn't give me an appropriate percent for maintenance?

    Reference: index fragmentation includes logical fragmentation and low page density from Pual's explanation - How record DELETEs can cause index fragmentation.

    Any thoughts?

    Thanks in advance! 


    MCSE:Data Platform

    Monday, December 16, 2013 8:01 AM

Answers

  • If you look at avg_fragmentation_in_percent, you will not get a direct correlation to avg_page_space_used_in_percent

    I do not know where you read that avg_page_space_used_in_percent is taken into account, but imho it is not. You can quite easily test it yourself.

    And this is why I do recommend looking at page density for index rebuilds as well. - Which Erland pointed out as well.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Marked as answer by Stephanie Lv Tuesday, December 17, 2013 3:03 AM
    Monday, December 16, 2013 8:10 PM
  • My question is, with a quite low page density, why the index fragment percent is so low? As page density is a part of index fragment (internal fragmentation). What we want is to reorganize this index when it's in a low page density, but the index fragment doesn't help.

    According to Books Online, the column avg_fragmentation_in_percent reports Logical fragmentation for indexes,. This concepted is explained later: This is the percentage of out-of-order pages in the leaf pages of an index. 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.

    Thus, there is nothing in this column that talks about page density. Many advices around index rebuilds centres around this value, but in a case like yours, you really want to look at avg_page_space_used_in_percent.

    One reason that avg_fragmentation_in_percent gets more spotlight is that is less expensive to compute, where as to compute page density every page needs to be looked at.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Tuesday, December 17, 2013 3:03 AM
    Monday, December 16, 2013 11:13 PM

All replies

  • I'm not exactly sure what your question is. From your description it is not unlikely that the table as a low level of fragmentation in terms of pages coming in the wrong order. But of course, the low page density is a problem, and in my opinion this is a greater problem of pages being in the wrong order. At least for an OLTP system, where you should not scan tables anyway. (It's different in a data warehouse.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 16, 2013 8:59 AM
  • Thank you for quick response!

    My question is, with a quite low page density, why the index fragment percent is so low? As page density is a part of index fragment (internal fragmentation). What we want is to reorganize this index when it's in a low page density, but the index fragment doesn't help.

    I agree with you that in our case, page order should not be problem. The index is Identity value, we only delete some old records, and new inserted rows are always increasing.


    MCSE:Data Platform


    Monday, December 16, 2013 10:07 AM
  • Thank you for quick response!

    My question is, with a quite low page density, why the index fragment percent is so low? As page density is a part of index fragment (internal fragmentation). What we want is to reorganize this index when it's in a low page density, but the index fragment doesn't help.

    I agree with you that in our case, page order should not be problem. The index is Identity value, we only delete some old records, and new inserted rows are always increasing.


    MCSE:Data Platform


    Hello,

    Before reorganizing or rebuilding index one more thing comes into picture which is page count of index.If pages count of index is <500 even if you rebuild it or reorganize it it wont effect its fragmentation.Sometimes it wont even change as a matter of fact even 99% fragmentation on these indexes will not cause performance problem as 100 or 500 pages can be reached quickly.

    I read the post by paul(SQL MAG) i think its answered your question why even sometimes with fill factor of 100 there is fragmentation.

    What query you are using to look into page density can you please post that query


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Monday, December 16, 2013 10:20 AM
  • Hi Shangky_621,

    Acutal data size is 24 GB in this table, it's a big table actually. I use sys.dm_db_index_physical_stats with DETAILED scan.


    MCSE:Data Platform

    Monday, December 16, 2013 10:43 AM
  • Hi Shangky_621,

    Acutal data size is 24 GB in this table, it's a big table actually. I use sys.dm_db_index_physical_stats with DETAILED scan.


    MCSE:Data Platform

    Sys.dm_index_physical_stats does not have any column as Page density.This is what worried me when you talked about page density.

    Can you clarify your question 



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, December 16, 2013 10:55 AM
  • Sys.dm_index_physical_stats does not have any column as Page density.This is what worried me when you talked about page density.

    Can you clarify your question 



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Hallo Shanky,

    it has :) It's named avg_page_space_used_in_percent!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, December 16, 2013 11:00 AM
  • Sys.dm_index_physical_stats does not have any column as Page density.This is what worried me when you talked about page density.

    Can you clarify your question 



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Hallo Shanky,

    it has :) It's named avg_page_space_used_in_percent!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Thanks Uwe I was not aware that its commonly referred as page density.Ya if you look at literal meaning both is same.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, December 16, 2013 11:18 AM
  • If you look at avg_fragmentation_in_percent, you will not get a direct correlation to avg_page_space_used_in_percent

    I do not know where you read that avg_page_space_used_in_percent is taken into account, but imho it is not. You can quite easily test it yourself.

    And this is why I do recommend looking at page density for index rebuilds as well. - Which Erland pointed out as well.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Marked as answer by Stephanie Lv Tuesday, December 17, 2013 3:03 AM
    Monday, December 16, 2013 8:10 PM
  • My question is, with a quite low page density, why the index fragment percent is so low? As page density is a part of index fragment (internal fragmentation). What we want is to reorganize this index when it's in a low page density, but the index fragment doesn't help.

    According to Books Online, the column avg_fragmentation_in_percent reports Logical fragmentation for indexes,. This concepted is explained later: This is the percentage of out-of-order pages in the leaf pages of an index. 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.

    Thus, there is nothing in this column that talks about page density. Many advices around index rebuilds centres around this value, but in a case like yours, you really want to look at avg_page_space_used_in_percent.

    One reason that avg_fragmentation_in_percent gets more spotlight is that is less expensive to compute, where as to compute page density every page needs to be looked at.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Tuesday, December 17, 2013 3:03 AM
    Monday, December 16, 2013 11:13 PM
  • I appreciate your clarifying Erland & Andreas!

    Yea, in most case, we only focus on avg_fragmentation_in_Percent, and it's my misunderstanding that avg_fragmentation_in_Percent is everything for logical/physical fragmentation.

    As you pointed Erland, we can get avg_fragmentation_in_Percent via LIMITED scan, it's less expensive, while DETAILED scan is needed for avg_page_space_used_in_percent.

    I think we have to implement another index maintence task for pay density with DETAILED scan, which is for large indexes only. Just keep the existing logical fragmentation maintenance with LIMITED scan for saving tiem and resource. They really should be dealt with respectively!

    Thanks again:)


    MCSE:Data Platform

    Tuesday, December 17, 2013 3:03 AM
  • ...

    I think we have to implement another index maintence task for pay density with DETAILED scan, which is for large indexes only. Just keep the existing logical fragmentation maintenance with LIMITED scan for saving tiem and resource. They really should be dealt with respectively!

    ...

    your're welcome

    also: if your data isn't very much scewed, a SAMPLED-scan does the same with a similar and often sufficiently accurate result - again, depends on the data


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, December 17, 2013 8:38 AM
  • Here is a repro that I composed last night, but which I did not get quite right and there for did not post it:

    CREATE TABLE albin (id int NOT NULL PRIMARY KEY,
                       filler char(950) NOT NULL DEFAULT ' ')
    go
    INSERT albin(id)
       SELECT TOP (8000) row_number() OVER(ORDER BY a.object_id, b.object_id)
       FROM   sys.columns AS a
       CROSS JOIN sys.columns AS b
    go
    DELETE albin WHERE id %8 <> 0
    go
    SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('albin'), 1, NULL, 'DETAILED')

    When you run it directly, page density is still good, because the rows are still there, as testified by the column ghost_record_count. After a while, all ghost records are deleted, and avg_page_used_in_percent shrinks to 12% while avg_fragmentation_in_percent is still 0.5.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 17, 2013 10:41 PM