How to identify fragmented index for rebuild

[xo55ox]

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.

[Dan Guzman]

Check out the white paper on SQL 2000 Index Defrag Best Practices:
http://www.microsoft.com/technet/pro.../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" <xo****@hotmail.com> wrote in message
news:ab**************************@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.