I've been new company for only a month and started analysing Index
Fragmentation.
After I ran DBCC DBREINDEX and capture data into permanent table, I 've
seen lots of tables with no indexes. These tables showed:
Very low scan density,
High extent fragmentation
High Avg. Bytes Free per Page
What are the best strategies to defragment tables with no indexes?
I'm planning to make a rule that each table must have a clustered index
and this index must be created on the best column (highest
selectivity).
Please help.
Thanks,
Silaphet,kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:
> I've been new company for only a month and started analysing Index
> Fragmentation.
> After I ran DBCC DBREINDEX and capture data into permanent table, I 've
> seen lots of tables with no indexes. These tables showed:
> Very low scan density,
> High extent fragmentation
> High Avg. Bytes Free per Page
> What are the best strategies to defragment tables with no indexes?
Create a clustered index on them. If the index is absolute undesired,
drop the index once your done.
> I'm planning to make a rule that each table must have a clustered index
> and this index must be created on the best column (highest
> selectivity).
Yes, that is a good rule. Heap tables with deletions can easily become
very fragmented.
As for which column to cluster on, you may want to find a column
that grows monotonically, if all you want to do is avoid fragmentation.
Of course, adding indexes to improve queries is a good idea too!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
