Exploring FILLFACTOR - Thomas Moore Way

[NewDBOnTheBlock]

Hello Everybody,

I was going through Thomas Moore's book on MS-SQL 2000. In one of the excercise questions on Indexes, here's a Q what he has come up with:

Q5: You have 50,000 records in a database file, and you know you want to add another 25,000 records in the next month. What FILLFACTOR should you specify to maximize performance? It should be mentioned that a new index is to be created and that you will change your FILLFACTOR; you also want fast INPUT into the tables.

0 (default setting)

100

70

50

A5: D. You know exactly how many new records are coming in. 25,000 is 50% of 50,000, so filling the page by 50% and leaving 50% free space for the remaining 50,000 records seems logical. For more details, see the section "Exploring FILLFACTOR."

I feel the answer should strictly be 66. And as per his requirement of 50K + 25K new records, total # records should equal 75K. FILLFACTOR for 50 can be considered as next candidate, but it's not an accurate answer.
-OR-
He failed to explain the scenario properly in his opening line of Q5 - which is more likely, as per other text from his book.

Any body interested in commenting?

[MAK]

It depends on how often you are re-indexing your tables. (Weekly Monthly?)

[NewDBOnTheBlock]

Why would that depend? .. if we know there are fixed number of records!

[MAK]

a. Are you going to import 25K rows on one go? In that case, I would drop the index, import that rows and re-create the index.

b. Are these rows are being inserted in between many rows [ Like PK=5 getting inserted between pk=4 and pk=6] or are they going to be appended?

c.If you are going to import one thousand rows a day then I would reindex the table often. You have to consider how many rows can be there in a page and in what sequence the insert happens.

d. Also how many non-clustered index are there in that table etc....

[NewDBOnTheBlock]

I have FedEx'd your questions to the author - Thomas Moore. I hope that's allowed in the cert. exam too.

lol.