Scan Density

Scan Density

Post by Tareq Loota » Tue, 16 Dec 1997 04:00:00



Hi Everyone,
I have three transaction tables in one of my databases where the primary
keys are clustered indexes.
At the end of every day, the scan density ( from DBCC SHOWCONTIG output)
become low ( around 15% ).
I am using DBCC DBREINDEX (table_name) to make it 100%. The next day, after
many transactions ( INSERT, UPDATE,DELETE) the scan density became 15%.
Is this a normal thing?
If it is normal, Should I schedule a task to do REINDEXing for me?

Thanks for your help in advance.

--
Tareq Lootah

 
 
 

Scan Density

Post by Tibor Karasz » Tue, 16 Dec 1997 04:00:00


Tareq,

You could use a fillfactor, so that your pages aren't totally flled when
you rebuild your indexes.
I don't know if this applies to you, but I'd check if the PK really is the
best candidate for your clustered index.
--

MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB



> Hi Everyone,
> I have three transaction tables in one of my databases where the primary
> keys are clustered indexes.
> At the end of every day, the scan density ( from DBCC SHOWCONTIG output)
> become low ( around 15% ).
> I am using DBCC DBREINDEX (table_name) to make it 100%. The next day,
after
> many transactions ( INSERT, UPDATE,DELETE) the scan density became 15%.
> Is this a normal thing?
> If it is normal, Should I schedule a task to do REINDEXing for me?

> Thanks for your help in advance.

> --
> Tareq Lootah



 
 
 

Scan Density

Post by Neil Pik » Tue, 16 Dec 1997 04:00:00


Tareq,

 Depending on how many page-splits you are doing, then yes, it is
possible to fragment the table this much.  If your app needs to do a
lot of scanning, then as you have suggested, REINDEX will help.

 Neil Pike MVP/MCSE
 Protech Computing Ltd (MS Solution Provider)

 
 
 

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. data env . refresh??? requery

3. Scan Density

4. West Coast - Architect/Data Modeler (Oracle, DB2, SQL Server)

5. Help Needed To Configure Server !

6. Scan Density doesn't change

7. Is there a book that documents Oracle's Thin JDBC?

8. syscomments table - low Scan Density

9. 'Scan Density' any ideas ?

10. Scan Density,Page splitting,heaps... Am I missing something?

11. Table scan, Table scan, Table scan

12. Clustered Index Scan vs. Table Scan