Post reply

Fillfactor, Indices and Tables, oh my!

  • February 23, 2005 at 12:33 pm

    #110105

    Ok, so I have an interesting issue.

    I've got a 10,000,000 row table, that had its fillfactor on its indices set at 15... which led to 8 gigabytes of index data. Yuch.

    I reconfigured to 90% fillfactor, dropped and rebuilt the indices.

    Now... when I run a dbcc showcontig on any individual index, it shows 90% page density. But.. if I run a dbcc showcontig on the table itself, it shows a 6% page density.

     

    What's up with that? How can I change the table's inherent fill-factor? Can you even do that? Am I misinterpreting the data?



    David W. Clary
    MCSD
    Data Miner 49er
    Sr. Database Administrator, Ceiva Logic

  • February 23, 2005 at 1:12 pm

    #543763

    Do you have a clustered index on the table, and if so, did you set the fillfactor on it also ? What is the rowsize of the table ?

     

  • February 23, 2005 at 1:19 pm

    #543765

    object id

    		 index name level pages rows avg free bytes avg density scan density Logical Frag Extent Frag
    picture_box 0 906987 10899252 7602.321 6.074613 93.74633 0.286663 0.331578
    picture_box picture_box_ie1 5 28404 10899266 805.264 90.0511 97.26102 2.82E-02 54.89207
    picture_box album_id 6 27043 10899267 841.374 89.60497 96.84904 0.040676 78.81904
    picture_box album_content_id 7 32864 10899269 799.719 90.11961 97.18476 8.22E-02 50.22997
    picture_box picture_box_content_id 10 22441 10899271 810.707 89.98386 99.11692 3.12E-02 45.49326
    picture_box picture_box_container_type_id 11 22530 10899269 839.492 89.62822 96.63808 0.048824 75.95204
    picture_box picture_box0 12 38830 10899269 798.003 90.14081 98.7991 2.06E-02 56.01966

     

    Above is the pertinent information from DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

    as you can see, object picture_box -- with no index name, is gargantuan in its # of pages, but with only 6% density.

    None of these indexes is clustered. Picture_box is a heavily used table with multiple (in the 10s of thousands) additions and deletions a day.



    David W. Clary
    MCSD
    Data Miner 49er
    Sr. Database Administrator, Ceiva Logic

  • February 23, 2005 at 1:33 pm

    #543772

    FillFactor only applies to indexes. So for a table with no clustered index, the table data pages aren't affected as you've seen. If you create a clustered index with fillfactor 90, this will solve the problem - but will also require careful selection of the clustered index column or columns.

  • February 23, 2005 at 1:38 pm

    #543773

    I'll give that a try. Thanks.



    David W. Clary
    MCSD
    Data Miner 49er
    Sr. Database Administrator, Ceiva Logic

  • February 23, 2005 at 1:44 pm

    #543775

    Remember, though, that when you add/rebuild a clustered index, ALL the other indexes get rebuilt at that time, so in order to avoid a huge performance hit, you should drop the other indexes first, build the clustered index, then rebuild all the non-clustered.

     

  • February 23, 2005 at 2:03 pm

    #543778

    LOL... too late! That's ok, rebuild time was just 19 minutes. I told Customer Service to go to lunch

     

    And... pages are down to 60K, instead of 900K.

     

    Thank you very much.

     



    David W. Clary
    MCSD
    Data Miner 49er
    Sr. Database Administrator, Ceiva Logic

  • February 24, 2005 at 1:47 am

    #543840

    See if this provides additional information:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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