How to identify fragmented index for rebuild

Hi,

I want to find out what is a good way to identify indexes that need
rebuilding. I tried to run DBCC showcontig and identify them based on
the scan density %. And according to a Microsoft Tech Net - All About
SQL Server Indexes discussion, the expert stated that based on the
Density (below 20%), I can pick out those indexes associated to the
table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
rebuild all indexes associated to the specific table. The strange part
is that some tables, which have 20% or below scan density do not have
any indexes. So, I am confused!

Thanks in advance.Check out the white paper on SQL 2000 Index Defrag Best Practices:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

> The strange part
> is that some tables, which have 20% or below scan density do not have
> any indexes. So, I am confused!

You can create an clustered index on a heap (a table with no clustered
index) in order to reorg the table. You can drop it afterward if it is not
needed. It's usually a good idea to have a clustered index on every table
unless you have a specific reason not to.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"xo55ox" <xo55ox@.hotmail.com> wrote in message
news:abbcb3d7.0404291344.14a3a778@.posting.google.c om...
> Hi,
> I want to find out what is a good way to identify indexes that need
> rebuilding. I tried to run DBCC showcontig and identify them based on
> the scan density %. And according to a Microsoft Tech Net - All About
> SQL Server Indexes discussion, the expert stated that based on the
> Density (below 20%), I can pick out those indexes associated to the
> table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
> rebuild all indexes associated to the specific table. The strange part
> is that some tables, which have 20% or below scan density do not have
> any indexes. So, I am confused!
> Thanks in advance.