Fillfactor, Indices and Tables, oh my!

Question

Fillfactor, Indices and Tables, oh my!

dclary

Old Hand

Points: 331
More actions
February 23, 2005 at 12:33 pm

#110105

Ok, so I have an interesting issue.
I've got a 10,000,000 row table, that had its fillfactor on its indices set at 15... which led to 8 gigabytes of index data. Yuch.
I reconfigured to 90% fillfactor, dropped and rebuilt the indices.
Now... when I run a dbcc showcontig on any individual index, it shows 90% page density. But.. if I run a dbcc showcontig on the table itself, it shows a 6% page density.

What's up with that? How can I change the table's inherent fill-factor? Can you even do that? Am I misinterpreting the data?

David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Do you have a clustered index on the table, and if so, did you set the fillfactor on it also ? What is the rowsize of the table ?

dclary Old Hand Points: 331 More actions · Answer 2

object id	index name	level	pages	rows	avg free bytes	avg density	scan density	Logical Frag	Extent Frag
picture_box		0	906987	10899252	7602.321	6.074613	93.74633	0.286663	0.331578
picture_box	picture_box_ie1	5	28404	10899266	805.264	90.0511	97.26102	2.82E-02	54.89207
picture_box	album_id	6	27043	10899267	841.374	89.60497	96.84904	0.040676	78.81904
picture_box	album_content_id	7	32864	10899269	799.719	90.11961	97.18476	8.22E-02	50.22997
picture_box	picture_box_content_id	10	22441	10899271	810.707	89.98386	99.11692	3.12E-02	45.49326
picture_box	picture_box_container_type_id	11	22530	10899269	839.492	89.62822	96.63808	0.048824	75.95204
picture_box	picture_box0	12	38830	10899269	798.003	90.14081	98.7991	2.06E-02	56.01966

Above is the pertinent information from DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

as you can see, object picture_box -- with no index name, is gargantuan in its # of pages, but with only 6% density.

None of these indexes is clustered. Picture_box is a heavily used table with multiple (in the 10s of thousands) additions and deletions a day.

David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

FillFactor only applies to indexes. So for a table with no clustered index, the table data pages aren't affected as you've seen. If you create a clustered index with fillfactor 90, this will solve the problem - but will also require careful selection of the clustered index column or columns.

dclary Old Hand Points: 331 More actions · Answer 4

I'll give that a try. Thanks.

David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

Remember, though, that when you add/rebuild a clustered index, ALL the other indexes get rebuilt at that time, so in order to avoid a huge performance hit, you should drop the other indexes first, build the clustered index, then rebuild all the non-clustered.

dclary Old Hand Points: 331 More actions · Answer 6

LOL... too late! That's ok, rebuild time was just 19 minutes. I told Customer Service to go to lunch

And... pages are down to 60K, instead of 900K.

Thank you very much.

David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

See if this provides additional information:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]