Fill Factor and Non-Clustered Index 1

[Davidmc555]

I'm currently making up a spreadsheet so my company can work out how large a table will be using the guide "Estimating the Size of a Table" in online books.

I also found the same document on MSDN online.
Estimate Table Size

It says in the document that "if a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. ... If no clustered index is to be created, specify Fill_Factor as 100."

Now I took that last bit as meaning either a nonclustered index or a heap. I also figured that if a nonclustered index worked like the index at the back of the book, it would look up the information needed from whatever page and just fill a page before going onto the next (i.e. no insertion mid page).

However, when looking at the guide for "Estimate the size of a table without a cluster index", it reads "Calculate the number of reserved free index rows per leaf page, based on the fill factor specified for the nonclustered index". Surely that's going to be 100%? I'm a little lost why I have to calculate this if what I understood was that a nonclustered table doesn't need a fill factor persay.

I've looked but can't find a definate answer to this and I'm sure I've gone of the trail or else I wouldn't be this confused, can someone explain it to me please?

 

[vongrunt]

Clustered index is considered integral physical part of table.

Nonclustered index is not.

First document you mentioned is about calculating table size, which doesn't include nonclustered indexes.

Reference from second document comes from "Calculate the Space Used to Store Each Additional Nonclustered Index" paragraph.

Makes sense?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Davidmc555]

*scrathes head*

Erm, not really...

I thought the first document was a preliminary to the other documents (Clustered/Nonclustered) as in you need to calculate the space the data takes up and then the index?

Given I've only been researching this for 2 days, I'm doing a lot of guess work here. Perhaps, an explanation in the basics of Indexes and Fill Factors might be the best thing to help me here.

Thanks again.

 

[vongrunt]

> Perhaps, an explanation in the basics of Indexes and Fill Factors might be the best thing to help me here.

OK.

Table without anything = heap table.

Nonclustered indexes are stored separately from table. Yes, they share some similarities with book index - but are not stored in book itself. Heap table with additional NC indexes still remains heap table.

Clustered index is stored within table... sort of. Basically it physically sorts data according to defined key column(s). Heap table with created clustered index becomes clustered table, with different size calculations. See step #7 from preliminary document for more information.

In addition, existance of clustered index also changes size calculations for nonclustered indexes. That's because key values from clustered indexes are also stored in NC index entries for lookup purposes.

So... this actually gives these steps:

1. estimate table size
2. estimate clustered index size (if any)
3. estimate size of additional nonclustered indexes (if any)

And formulas for 1 and 3 are different depending on whether table has clustered index or not.

Fill factor is something that applies only during index creation. 100% means "no gaps"; smaller percentages create additional initially unused space and therefore increase estimated size. Fill factor applied to clustered index affects table size calculation - because data rows are stored in leaf clustered index pages. Each index - clustered or not - may have it's own fill factor.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Davidmc555]

Ok, that's filled in plenty of holes there was, thanks.

By formulas for step 1 and 3, how would they differ exactly?

The tables I'm dealing with (I think they are clustered and non-clustered by looking at 'sp_help <table_name>' and look at the index details) should be mostly clustered with some non-clustered.

I've generated a spreadsheet where it has 2 tabs. The first tab is for non-clustered and follows Step 1 (as in Steps in post above) followed by "Estimating the Size of a Table without a Clustered Index" (I'm assuming Step 3 here) and adding up the totals from each index.

The second tab is for Clustered Indexes and follows Step 1 and then Step 2.

If that's the case, would the spreadsheet be a good estimate or have I fluffed it all up?

Finally, I take it then if Non-clustered Indexes can have Fill Factors then that means they aren't necessarily 100%?

Thanks again, I appreciate it.

 

[vongrunt]

> By formulas for step 1 and 3, how would they differ exactly?

1 - only by step #7 described in preliminary doc. 3 - a little bit more ("clustered" version is relatively complicated)

To avoid future confusion... forget about these all-in-one steps and follow MSDN documents:

- doc 2 for heap table
- doc 3 for clustered table.

Both include preliminary (1) document as a first step, so your spreadsheed seems to be OK.


> Finally, I take it then if Non-clustered Indexes can have Fill Factors then that means they aren't necessarily 100%?

Yup. You can specify different value (CREATE INDEX... WITH FILLFACTOR=)... or server-wide default (sp_configure 'fill_factor') may be different than 100%.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Davidmc555]

Brilliant, thank you. Almost finished the thing and noted that the there is a small variation in the steps for non-clustered index between doc2 and doc3.

Anyway, thanks again, have a good weekend.