Opteron performance with reindex operation

Hi,

We have been migrating some of our itanium servers to Opteron. With the last migration, we move our Siebel analytics server to a 4 way Opteron (4 dual core) with 64 GB RAM - SQL server Enterprise edition 32 bit, with AWE enabled. Every task that is executed against this new server (queries, nightly data ETL jobs etc) have shown a massive improvement in performance with the sole exception of a reindex job that creates a clustered index (using with drop eixsting option) on one the the large ETL tables (6 - 8 million rows, clustered index is 7 columes wide, 2 varchar (30), 3 numeric, 2 datetime). The reindex job runs after the ETL process that loads the data into the table. The ETL modifies approx 30% - 40% of the table data, so fragmentation is severe after the ETL load. The prior reindex method that we were using (db reindex rather than a complete rebuld took much longer to complete). Also, dropping the clustered index before the ETL is not supported (although it is outside the scope of this question, I thought I would mention it in case anyone was wondering why we didnt drop the index before loading the data).

I can run the same reindex job against the same data set on a dual zeon with 6 GB of RAM in 26 minutes... On the Opteron it takes almost 2 hours.....

Performance in every other aspect is phenomenal Any help would be appreciated.

Hi Mike,

Please let us know if this issue is now resolved.

It could be that the problem is IO related on your new server. but then again everything else runs fine.

Are you able to tell if any of the susbsystems (IO, memory, CPU) are under pressure during the re-index. What the re-index process is mostly waiting on?

Is it SQL 2000?

What is the recovery model you use during the re-index on both the boxes?

regards

Jag

|||

Are you running on SQL Server 2005 or SQL Server 2000. Especially if you have 64GB of RAM, you might want to consider using the 64-bit version instead of using 32-bit with AWE, since it lets you use your memory more effectively than 32-bit with AWE.

Does it look like you are seeing memory pressure or IO pressure during the clustered index rebuild? Do you have the "Sort in TempDB" option enabled for the index?

Perhaps the index rebuild is spilling over to disk (where the index is stored or in TempDB). Microsoft recommends having one TempDB file per processor (and dual core processors count as two).