By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,882 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,882 IT Pros & Developers. It's quick & easy.

Defragment Heap Tables

P: n/a
Hi guru,

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,

Oct 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
km********@yahoo.com (sm********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.