Average Page Sizes and Density

[bombplayer]

How do I reduce or actually increase the average page density of a data file or index?

I have a large file with 3 mill entries that has a page density of roughly 10 percent through all 12 indexes in the file.

I can't imagine that this is using my resources very efficiently as the datafile is 20 gig and it was 7 gig a few weeks ago.

Here is the printout from dbcc showcontig:

Table SaTrans
Index IX_SaTrans
Pages 40417
Rows 1295535
MinRecSize 13
MaxRecSize 24
AvgRecSize 23.736
Extents 5071
Extent Switches 5070
Avg Free Bytes 7271.051758
Avg Page Density 10.16738605
Scan Density 99.64504043
Best Count 5053
Actual Count 5071
Logical Fragmentation 2.875027895
Extent Fragmentation 7.769670486



Any help would be appreciated....

 

[rt63]

There are quite a few issues with the file/database size.

1. You can reindex the table using fill factor closer to 100. 100 would mean fully occupied pages but will make your table insert/update statements a little slower. You need to reindex on a regular basis.
2. The database can grow because you may not be shrinking database on a regular basis.
3. Sometimes, the free data in a database does not get automatically released to OS even after a shrink database command. You need to copy the table, truncate and copy back the data to release space.
4. Another thing I observed in your table - there seem to be some variable size fields in your table (min rec 13 and max 24) and the average size is 23.76 which is close to max. It will be better if you make all fields fixed size - will further reduce your storae requirements.

Hope it helps


RT