Help on SQL server 2000 in rebuilding index

[elmer0727]

Hi,

We have an SQL Server 2000 with one 138GB database currently used. I am worry of the performance because as time goes it becomes slower and slower. So I tried googling out the solution and I found out the DBCC Reindex command. As what I read, first you have to know if the table is really fragmented so you have to use DBCC SHOWCONTIG to know it.

First step I make is: DBCC SHOWCONTIG ('tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
step 2: DBCC DBREINDEX ('dtrf') WITH NO_INFOMSGS
step 3: DBCC SHOWCONTIG ('dtrf') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

but when I compare the results of dbcc showcontig there is one index that doesn't change anything with greater value of logicalfragmentation.

I attached a jpg file to understand the result well as I am not really good in english words construction.

Question is: Is it normal result or is theres something wrong with it?

Hope somebody can help me..

[cutepinkbunnies]

The problem is you are looking at a heap, which can't be organized. This is of course providing that the query you executed returns simliar results to 2008/2005 queries...
IndexID of 0 = heap
IndexID of 1 = clustered index
IndexID >= 2 = nonclustered index

Here's an article on indexing 101. You have 6 nonclustered indexes that reference a heap.
http://sqlserverplanet.com/indexes/sql-server-indexes-tutorial/

Using that article to create an appropriate indexing strategy (to include a clustered index) will most likely help your performance.

Any query that isn't satisfied by the nonclustered indexes is forced to perform a table scan (leaf level) on the data, which is terribly inefficient.

Here's another article that discusses heaps.

http://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables/

Most database people strongly discourage tables that have no clustered index in their environments.