A Self-Tuning Fill Factor Technique for SQL Server – Part 2

  • Mike Byrd

    Ten Centuries

    Points: 1268

    Comments posted to this topic are about the item A Self-Tuning Fill Factor Technique for SQL Server – Part 2

    Mike Byrd

  • Thomas Franz

    Hall of Fame

    Points: 3593

    Nice article.

    My main problem with the fill factor is, that I can't set it per partition. So I have either to waste a lot of space in my old, cold data from 3 years ago that will never be updated / inserted again or have a low fill factor and many splitts on my current hot data with a fill factor of e.g. 99 %...

    God is real, unless declared integer.

  • rgloeckner

    Newbie

    Points: 2

    very interesting, indeed.

    at least with additional non-clustered indexes you can use different, filtered ones. one for the hot data, another for the cold data. you will need to be creative if reporting stretches into the cold data, though.

  • Mike Byrd

    Ten Centuries

    Points: 1268

    Stand by for an undated script in GitHub.  You can find it with my UserName: MByrdTX

    I also have listed there this 2 parter in Word format, a PowerPoint presentation I gave last week to the Austin SQL Server User's Group (CACTUSS), and a link to a video created for my Austin presentation.  Newer scripts are there from the one submitted with Part 1, but standby for the new one later this week.

    The new script should be uploaded NLT the end of the week.  I am still doing some testing with additional inputs from previous reviewers.

    I feel the pain of not being able to specify different fill factors for partitions within an index.  It should be on Microsoft's wish list.

    Cheers,

    Mike

    Mike Byrd

  • Jeff Moden

    SSC Guru

    Points: 995467

    Apologies... I posted a reply on the wrong thread and removed it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995467

    Thomas Franz wrote:

    Nice article.

    My main problem with the fill factor is, that I can't set it per partition. So I have either to waste a lot of space in my old, cold data from 3 years ago that will never be updated / inserted again or have a low fill factor and many splitts on my current hot data with a fill factor of e.g. 99 %...

    That's one of the reasons why I prefer Partitioned VIEWS compared to Partitioned TABLES.  They have a lot of other advantages over Partitioned tables, as well.  I hope MS doesn't see fit to someday deprecate them like they have a whole lot of other incredible tools.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

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