Scan Density doesn't change

Scan Density doesn't change

Post by Simon Balleno » Fri, 17 Nov 2000 04:00:00



Good Morning,

I have a 6.5 server that has multiple tables with poor scan density.  I did
a dbreindex of the clustered index which rebuilt the clustered index as well
as all non clustered indexes.  A subsequent dbcc showcontig shows that the
scan density did not change.  I tried the rebuild multiple times and I also
tried a slightly different fill factor all to no avail.

Any ideas would be appreciated.

Thanks

Simon Ballenot

 
 
 

Scan Density doesn't change

Post by Kalen Delane » Fri, 17 Nov 2000 04:00:00


Can you post the output of dbcc showcontig?

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:> Good Morning,

> I have a 6.5 server that has multiple tables with poor scan density.  I
did
> a dbreindex of the clustered index which rebuilt the clustered index as
well
> as all non clustered indexes.  A subsequent dbcc showcontig shows that the
> scan density did not change.  I tried the rebuild multiple times and I
also
> tried a slightly different fill factor all to no avail.

> Any ideas would be appreciated.

> Thanks

> Simon Ballenot


 
 
 

Scan Density doesn't change

Post by Simon Balleno » Wed, 22 Nov 2000 04:00:00


DBCC SHOWCONTIG scanning 'transactions' table...
[SHOW_CONTIG - SCAN ANALYSIS]                                              
---------------------------------------------------------------------------
Table: 'transactions' (201051752)  Indid: 1  dbid:6
TABLE level scan performed.
- Pages Scanned................................: 24328
- Extent Switches..............................: 22289
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 13.64% [3041:22290]
- Avg. Bytes free per page.....................: 767.2
- Avg. Page density (full).....................: 61.90%
- Overflow Pages...............................: 0
- Disconnected Overflow Pages..................: 0
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

* Sent via Developersdex.com http://www.developersdex.com *
The Web Developers Index

 
 
 

1. FILLFACTOR (SCAN DENSITY VS. AVERAGE PAGE DENSITY)

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

2. Access Control on host address level

3. Scan Density

4. RPC failure

5. DTS error

6. syscomments table - low Scan Density

7. Change INTERNAL password

8. Scan Density

9. index scan with index cond on first column doesn't

10. scan doesn't work with switchIndex and setRange

11. Why index scan doesn't work ???

12. class doesn't support automation or doesn't support expected interface