Fill Factor on Clustered Index

Question

Fill Factor on Clustered Index

Arsh

SSCertifiable

Points: 6345
More actions
September 15, 2017 at 3:58 am

#323566

Dear Experts,
When creating a clustered index , does it matter how much are the updates done and then decide on the fill factor ? Since the clustered index key is a very unique column in a table mainly created to have ordered-uniqueness (like Patient ID, for example) . Aren't updates out of question to be considered for page-splits , as these column will not get updated ? Please share your ideas. Thank you.

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

What I think I remember about page splits is that it's dependent on your row size. If you happen to have a row size where an 8K page divides exactly evenly into a set number of rows, then no page splits could ever occur for that table - however - that's not particularly likely. I'm not sure about how updates affect page splits, but I think it's when a row has to grow because of a varchar or nvarchar field being updated and the number of characters in the row thus increases, or something like that. I'd have to go research that stuff to be sure, but I'm also pretty sure someone here will straighten me out if I'm even slightly off kilter with that explanation, and I'll happily learn from it. 🙂

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

jasona.work SSC Guru Points: 50083 More actions · Answer 2

Fill factor is also important when it comes to inserts. Using your PatientID as an example, if your application numbers patients by 10s (so 10, 20, 30...) and "child accounts" by 1s, if you have a 100% fill factor, you will run into page splits.

So when PatientID 12340 gets married and adds their spouse to their plan, resulting in PatientID 12341, you can get a page split.

As for updates, ideally you would be correct and never update your clustered index value.
Ideally...

Arsh SSCertifiable Points: 6345 More actions · Answer 3

jasona.work - Friday, September 15, 2017 7:37 AM
Fill factor is also important when it comes to inserts. Using your PatientID as an example, if your application numbers patients by 10s (so 10, 20, 30...) and "child accounts" by 1s, if you have a 100% fill factor, you will run into page splits.
So when PatientID 12340 gets married and adds their spouse to their plan, resulting in PatientID 12341, you can get a page split

Thanks sgmunson and jasona.work . Yeah updates ideally should never happen for clustered index key. As for the inserts , Jasona , doesn't it depend upon the position of the patient ID 12340 on the index page ? like if it were at the end , page split wouldn't be required . And even if it were in the middle , isn't the ID already used by the patient next to 12340 already ? Or am I missing the trick ? Oh.. Seems I got it.. you mean if a page has patientID's 12340 to 12345 for example with , say 90% fill factor , the addition of the spouse patientID 12341 can be accommodated in the same page ? Am I right in understanding this ? Hope yes.. Thanks again Jasona.

jasona.work SSC Guru Points: 50083 More actions · Answer 4

Arsh - Friday, September 15, 2017 8:39 AM
jasona.work - Friday, September 15, 2017 7:37 AM
Fill factor is also important when it comes to inserts. Using your PatientID as an example, if your application numbers patients by 10s (so 10, 20, 30...) and "child accounts" by 1s, if you have a 100% fill factor, you will run into page splits.
So when PatientID 12340 gets married and adds their spouse to their plan, resulting in PatientID 12341, you can get a page split
Thanks sgmunson and jasona.work . Yeah updates ideally should never happen for clustered index key. As for the inserts , Jasona , doesn't it depend upon the position of the patient ID 12340 on the index page ? like if it were at the end , page split wouldn't be required . And even if it were in the middle , isn't the ID already used by the patient next to 12340 already ? Or am I missing the trick ? Oh.. Seems I got it.. you mean if a page has patientID's 12340 to 12345 for example with , say 90% fill factor , the addition of the spouse patientID 12341 can be accommodated in the same page ? Am I right in understanding this ? Hope yes.. Thanks again Jasona.

Fairly close.
Think of it as an Excel spreadsheet, with a formula that adds 10 to the previous value for a cell. SQL will try to cram as many of those values in as possible, leaving no "space" between values. Now, you need to put in a record with a value of 15. SQL can't put it on the "end" of the records, it's out of sequence, so it needs to make room between 10 and 20 for it by creating a row. With a 100% fill factor, some rows are going to need to go on a new page, so you get a page split. If you had a fill factor less than 100%, depending on the data in the rows you may or may not get a split.

If I weren't at work, I'd work up a nice graphic representation of this for you.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 5

UPDATEs can indeed cause page splits, in particular if lengthening the value of a varchar column, which is quite common. Often the resulting potential page split issues get overlooked in that scenario.

For example, say you have a 99% fill factor -- normally OK on a sequential-id table -- leaving 80 bytes or so free on the page. Then say you expand a varchar column(s) by 20 bytes using UPDATE. If you have more than 4 rows per page, that could cause a page split. Of course you still don't want too low a fill factor, especially for older, stable rows.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

The FILL FACTOR really only comes into play if you make the mistake of rebuilding or reorganizing the Clustered Index. Stop that and let the "Natural Fill Factor" be the rule. Just to put my money where my mouth is, I've not done any index maintenance on my production boxes since 17 Jan 2016 and performance actually got better in the first 3 months and has not degraded since then. All of the considerations about page splits and memory are very true but that's only because people keep enabling large volumes of page splits by doing index maintenance.

--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.

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)

Arsh SSCertifiable Points: 6345 More actions · Answer 7

jasona.work - Friday, September 15, 2017 8:53 AM
Arsh - Friday, September 15, 2017 8:39 AM
jasona.work - Friday, September 15, 2017 7:37 AM
Fill factor is also important when it comes to inserts. Using your PatientID as an example, if your application numbers patients by 10s (so 10, 20, 30...) and "child accounts" by 1s, if you have a 100% fill factor, you will run into page splits.
So when PatientID 12340 gets married and adds their spouse to their plan, resulting in PatientID 12341, you can get a page split
Thanks sgmunson and jasona.work . Yeah updates ideally should never happen for clustered index key. As for the inserts , Jasona , doesn't it depend upon the position of the patient ID 12340 on the index page ? like if it were at the end , page split wouldn't be required . And even if it were in the middle , isn't the ID already used by the patient next to 12340 already ? Or am I missing the trick ? Oh.. Seems I got it.. you mean if a page has patientID's 12340 to 12345 for example with , say 90% fill factor , the addition of the spouse patientID 12341 can be accommodated in the same page ? Am I right in understanding this ? Hope yes.. Thanks again Jasona.
Fairly close.
Think of it as an Excel spreadsheet, with a formula that adds 10 to the previous value for a cell. SQL will try to cram as many of those values in as possible, leaving no "space" between values. Now, you need to put in a record with a value of 15. SQL can't put it on the "end" of the records, it's out of sequence, so it needs to make room between 10 and 20 for it by creating a row. With a 100% fill factor, some rows are going to need to go on a new page, so you get a page split. If you had a fill factor less than 100%, depending on the data in the rows you may or may not get a split.
If I weren't at work, I'd work up a nice graphic representation of this for you.

Thanks Jasona for the beautiful example. Got it. Could visualize the Excel example nicely put by you. Glad if you can share your web / blog like to follow. Thanks to Jeff and Scott too.