FILLFACTOR (SCAN DENSITY VS. AVERAGE PAGE DENSITY)

FILLFACTOR (SCAN DENSITY VS. AVERAGE PAGE DENSITY)

Post by SQL SERVE » Thu, 29 Jan 2004 22:15:52



Scenario:
 Created a clustered index and nonclustered indexes on a
table with 5,953,675 rows using a fillfactor of "95" for
clustered index and "98" for nonclustered indexes.
  After 82,119 inserts (.01 of table), the nonclustered
indexes had a value of "83" for scan density, "4" for
logical frag, and "96" for avg. page density.  The
clustered index had a value of "96" for scan density, "1"
for logical frag, and "88" for avg. page density.
  Methinks I should have used a fillfactor for the
nonclustered indexes a value of "96", and a fillfactor
value of "96" for the clustered index.  The table and
indexes span multiple files.  Confused on trade-off on
scan density versus avg. page density with nightly inserts.
Am in an OLAP (Data Warehouse) environment.  Have other
tables with similiar scenarios (roughly one percent of
table being done nightly with inserts).
  Am open to anything.  Thank you
 
 
 

FILLFACTOR (SCAN DENSITY VS. AVERAGE PAGE DENSITY)

Post by Uri Diman » Fri, 30 Jan 2004 08:13:07


Look, you can get a good info/feel about what value to assign for fill
factor by monitoring  physical disk writes and reads
If your writes are about 10%  you may want to consider lower  fill factor
otherwise use default fillfactor

I have read some articles abouit it.
if you have high update/insert oltp applicatioin use 70-80% for fill factor.
But I always say it depends on particular situation/query/business
requirements  and you have to test by yourself.


Quote:> Scenario:
>  Created a clustered index and nonclustered indexes on a
> table with 5,953,675 rows using a fillfactor of "95" for
> clustered index and "98" for nonclustered indexes.
>   After 82,119 inserts (.01 of table), the nonclustered
> indexes had a value of "83" for scan density, "4" for
> logical frag, and "96" for avg. page density.  The
> clustered index had a value of "96" for scan density, "1"
> for logical frag, and "88" for avg. page density.
>   Methinks I should have used a fillfactor for the
> nonclustered indexes a value of "96", and a fillfactor
> value of "96" for the clustered index.  The table and
> indexes span multiple files.  Confused on trade-off on
> scan density versus avg. page density with nightly inserts.
> Am in an OLAP (Data Warehouse) environment.  Have other
> tables with similiar scenarios (roughly one percent of
> table being done nightly with inserts).
>   Am open to anything.  Thank you


 
 
 

1. Reducing the Avg Page density of a page

I have a SQL server 6.5 installation.
One of the tables has an average page density of 55%.
How can I increase the average page density of this table.

Table's row size is around 252 bytes.
It has 1.3 million records.

I tried reindexing, and dropping and recreating index.
thanks
ChandraPrakash

2. Enumerating Sybase Databases (Update to prev post)

3. Scan Density

4. keycode

5. Need a Column to Identify the Row Number

6. Delphi/Career Position/ILL

7. Scan Density doesn't change

8. syscomments table - low Scan Density

9. Avg. Page Density Issue with Copy Objects

10. page density 1% !

11. Density Calculation on the statistics page.