Rebuild Primary Key Indexes for performance 1

[topub]

Hi there,

I have a sql server 2000 database with about 5 million records. The table has about 30 columns and 10 indexes.

Clustered index on PK
Non-Clustered indexes on other important columns

DBCC SHOWCONTIG scanning 'MyTable1' table...
Table: 'MyTable1' (699149536); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 70408
- Extents Scanned..............................: 8817
- Extent Switches..............................: 9175
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 95.91% [8801:9176]
- Logical Scan Fragmentation ..................: 62.67%
- Extent Scan Fragmentation ...................: 0.88%
- Avg. Bytes Free per Page.....................: 744.4
- Avg. Page Density (full).....................: 90.80%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The performance on queries that run against this table are very slow. Sometimes more than 3 minutes. The queries return a portion of the table with JOIN's to other small tables [other tables have 100 records]

After rebuilding all the non-clustered indexes, there was a slight improvement in performance. But the "Logical Scan Frangmentation" is still large.

Also Scan Denesity, Extent Scan Fragmentation and Avg. Page Density look good to me. But is it good enough.

Note: Because its a small DB we have it on a RAID-5 [log and data].

Please suggest what I could do to make things better here.

Also, I've read books on performance, but all they talk about it indexes and h/w arrangement. Is that all there is to optimization of a tables performance. [Assuming the query is good]


Thanks,
UB

 

[ptheriault]

You need to re-index you Clustered index. Re-indexing non-clustered indexes does not correct fragmentation. Because of the size of this table I would do it off hours. It is going to take some time.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[topub]

thanks for quick resoponse

Isn't reindexing clustered index like re-creating table [drop and create with new options].

or is there some option like

alter index ....
  REBUILD

like in Sql Server 2005

 

[ptheriault]

>>Isn't reindexing clustered index like re-creating table
Um..no

You are using SQL 2000, Correct?

You need to run DBCC DBREINDEX()

This will rebuild the Clusterd Index in your Transaction log. (For a large database or Table your log could get very large after the reindex)

You data is physicaly stored in the leaf level of your clustered index. Basically fragmentation means that your data is spread all over your disk instead of being in sequential order. When you run DBCC DBREINDEX it creates a new index and replaces the old one. However, your table is not dropped and recreated.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[topub]

thanks.

I thought I knew 'DBCC DBREINDEX' command very well. But you never know.

I'll try it and see how it goes.

thanks again,
_UB

 

[ptheriault]

Here is what REINDEX will do.

Rebuilds one or more indexes for a table in the specified database.

DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means an index can be rebuilt without knowing the structure of a table or its constraints. This might occur after a bulk copy of data into the table.

DBCC DBREINDEX can rebuild all the indexes for a table in one statement. This is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is performed by one statement, DBCC DBREINDEX is automatically atomic; whereas, individual DROP INDEX and CREATE INDEX statements must be included in a transaction to be atomic. Also, DBCC DBREINDEX offers the advantage of more optimizations than individual DROP INDEX and CREATE INDEX statements.

Unlike DBCC INDEXDEFRAG, or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline. Use the ALTER INDEX REBUILD statement with the ONLINE option to perform an index rebuild online, or to control the degree of parallelism during the index rebuild operation.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[topub]

You are the nicest.

thanks a lot again,
_UB