Share via

MSSQL Fragmentation

sakuraime 2,321 Reputation points
2020-11-12T05:45:38.17+00:00

39243-frage.jpg

what's the relationship between avg_fragmentation_in_percentage vs fragment_count / avg_fragment_size_in_pages

I know Page_count / fragment_count will be equal to avg_fragment_size_in_pages

But how to relate to avg_fragmentation_in_percentage in %????

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,148 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,271 Reputation points
    2020-11-12T07:06:13.357+00:00

    Hi @sakuraime ,

    what's the relationship between avg_fragmentation_in_percentage vs fragment_count / avg_fragment_size_in_pages

    I know Page_count / fragment_count will be equal to avg_fragment_size_in_pages

    But how to relate to avg_fragmentation_in_percentage in %????

    Maybe they have a subtle relationship, but I didn't find a suitable algorithm or formula. And I think the avg_fragmentation_in_percentage is the Logical Scan Fragmentation in DBCC SHOWCONTIG('TABLENAME').

    39245-20201112dbccshowcontig2.jpg
    39246-20201112dbccshowcontig.jpg

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.

  2. m 4,271 Reputation points
    2020-11-13T01:22:37.313+00:00

    Hi @sakuraime ,

    ...so the avg_fragmentation (logical fragmentation) calculation is MS internal and not for public ?

    I can't find the relevant document explanation on the official website for the time being, but I can help ask my superiors and reply to you.

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments