Fillfactors and Splitting tables

Question

Post reply

Fillfactors and Splitting tables

tendayit

Ten Centuries

Points: 1348
More actions
November 20, 2008 at 7:36 am

#132219

Hi,
1. When splitting a table into multiple ones, what is the calculation used to determine how many records should be in each new table and what the fill factor for each table should be?
2. Is there a calculation that can be used to determine if a fill factor for a table with x amount of records is right or wrong, and what should be the right one? Should a dba check this sort of thing weekly for fast growing tables?
3. What calculation is used to determine what size a table or database will be in say 5 years time?
Thanks
Tendayi

Viewing 4 posts - 1 through 3 (of 3 total)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

splitting tables uses partitioning.

Fill factors are used for index build operations and determine the fill level of the leaf pages.

do you mean partitioning tables?

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

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

tendayit Ten Centuries Points: 1348 More actions · Answer 2

Hi,

Sorry for the late response. Yes, i mean partitioning. Say you have a transaction table with 80 million rows and you want to partiotion it into 6 tables. How do you go about calculating how many rows should be in each table and what fill factor each should use. How do you identify that a wrong fill factor has been used?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

When you partition the data, you partition it along set data boundaries and not by rows. For instance for E-commerce data, you could partition it on month boundaries, or you could partition it by OrderID in 10000 ID blocks. If you had a Header/Detail table pair, you would want to partition the detail data along the same boundaries as the header, which would logically put more data in some partitions depending on order sizes.

Fill factors are used to leave free space at the leaf level to help minimize page splits from inserts and updates to the table. Determining the appropriate fill factor isn't an exact science. You have to know your data, and know the way it is going to be created/used to determine what might be a good fill factor. For example on a auto incrementing column like an identity or a newsequentialid() based column, you don't need much free space at the leaf level because the rows are being stored sequentially into the table, so end page splits will occur which aren't problemattic. However if you have an indexed varchar column or a uniqueidentifier that is not using the newsequentialid() function to generate its id's, you get data that is fairly random in nature and since indexes store data ordered, you end up with middle page splits that impact performance because 1/2 of the rows on the page are moved to a new page so that the new row can be inserted onto the original page.

There currently isn't a very efficient way to go about monitoring this. I requested an addition to the XEvents engine in 2008 that would allow tracking of the type of splits occuring to the allocation_id level that would allow you to better track it:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=388482

For now, you are kind of in a guess and check situation. Monitor the Page/Splits\sec counter, but then monitor index fragmentation between rebuilds. If you see fragmentation climbing rapidly, you probably are experiencing page splits on the index, and you might try lowering the fill factor to see if that helps it out any. It is more of an experiment and see kind of scenario, but you should see less fragmentation, or it happening slower, and lower page/splits\sec in the perf counters if you are making improvements.