Post reply

I can t reduce Avg. Page Density

  • January 12, 2012 at 11:20 pm

    #266427

    I have table I rebuild index but ı can t reduce fragmentaion is it relation between amount of page

    TABLE level scan performed.

    - Pages Scanned................................: 211

    - Extents Scanned..............................: 31

    - Extent Switches..............................: 30

    - Avg. Pages per Extent........................: 6.8

    - Scan Density [Best Count:Actual Count].......: 87.10% [27:31]

    - Logical Scan Fragmentation ..................: 8.53%

    - Extent Scan Fragmentation ...................: 61.29%

    - Avg. Bytes Free per Page.....................: 1419.9

    - Avg. Page Density (full).....................: 82.46%

  • January 13, 2012 at 2:01 am

    #1433064

    What is the fill_factor of your index ?

    See this post : http://www.sqlservercentral.com/Forums/Topic230400-5-1.aspx

  • January 13, 2012 at 2:22 am

    #1433080

    80

  • January 13, 2012 at 2:24 am

    #1433082

    Hang on, why would you want to reduce page density? Page density is the average % of used space on pages, higher is usually good.

    The reason it's around 80 is that you asked for it to be in that area. The fill factor (the % of the page used after an index rebuild) is set to 80, so after a rebuild the average page density will be around 80%, in this case 82%

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 13, 2012 at 2:36 am

    #1433090

    Sorry i want to make fragmention of index about 100

  • January 13, 2012 at 2:37 am

    #1433091

    I drop and create index rebuild index reorganize index but i could not do it

  • January 13, 2012 at 2:46 am

    #1433096

    If you want the page density close to 100, then you'll have to set the fill factor to 100. SQL's not going to fill pages 100% when you've explicitly told it (with the fill factor) to make the page density around 80.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 13, 2012 at 4:51 am

    #1433141

    ESAT ERKEC (1/13/2012)

    Sorry i want to make fragmention of index about 100

    fragmentation should be as low as possible not as high as possible!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 13, 2012 at 5:37 am

    #1433160

    I want to make it but i can t explain it maybe a lot of time i drop and create index rebuild and reorganize but i could not

  • January 13, 2012 at 5:50 am

    #1433166

    What are you trying to do? Your posts are not making any sense.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 13, 2012 at 6:19 am

    #1433181

    GilaMonster (1/13/2012)

    You're posts are not making any sense.

    It's not just me then 🙂

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply