Optimize table/query performance with indexes

[topub]

Hi there,

[not sure if this is the right category, but]

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]

Finally, is rebuilding clustered index an option here [I know its not a recommended option, but if there is no other way then....] Ideally, I'd like to change the FILLFACTOR of the table to 60% (heavy read & lot of inserts).

Thanks,
UB

 

[ptheriault]

This is a duplicate thread.

thread183-1349277

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

 

[topub]

Is there a way to delete this thread?
_UB

 

[ptheriault]

The Admins will do that.


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