Fill Factor, Why Do You Taunt Me? Part 3

Let's look at something a bit more in depth. Let me start by saying, "Please don't arbitrarily change any default setting without understanding it fully, meaning how it's used, what it does, what impacts will exist, and how much it could hurt." Now! for something interesting.

First we need to create our table.

I create most of my tables in a DBA only Database. I've worked in places where you weren't allowed to create any Databases for any reason ever without 3 levels of permission, a signed letter with a blood seal and two recommendations from two separate Illuminati knights. OK, maybe it wasn't that bad... but it definitely felt like it. Place this where ever you want, just modify the scripts accordingly.

Now we need to fill out the table.

This will take some time. Get some coffee, find a new book, plant a tree. I'm hoping to find a faster way to get this same data, but as it stands... pulling this information off of a 5TB + database is a days event. The good thing is that this data will be good for quite some time.

Note: This is something I would suggest addressing on a yearly basis in a slow moving environment, or monthly in a volatile one.

Now for some fun. we need to query our table.

Note: I realize that a table has the ability to use up to 8096 bytes if you're using multiple records. This includes other overheads I did not calculate. 8060 was used to allow that 36 byte area to be used just for overhead.

You should only be concerned with the tables that you know to be active. I will add a way to track that as well in another post that will marry this concept with a true use concept. you may find one that looks a bit off.

if your max record size is 3908, you are only going to get 2 on a page. If you really want every page to be able to insert two records.... you need a fill factor of 51%. That's a good time to just be happy with page splits and let it go.

If you use the query built off AVG, remember that an average item is 50%. It'll catch... well half roughly. Take that into consideration.

Note: It could also help you identify if you have one extremely large record and a bunch of tiny ones. This could be something to investigate.

--select * from master.dbo.FillFactorStatistics

It's interesting to me at least.

Kendra Little has a wonderful post on fill factors.

There's a white paper on them by Ken Lassesen.

Karel Coenye has a Technet video with more detail.