configuration issue on new server.

[Jim]

Hi,

I have been asked to help in a situation where a sql server (ver 6.5 - running on NT 4.0) was migrated to a new machine. This machine has 4 processors and about 2 G of memory.

The problem is that queries are running a lot longer than they used to. A complex query that used to take approx 4 minutes to complete on the old machine now takes over 11 minutes on the new machine. These are canned querries produced by a reporting system, and are not able to be optimized. The indexes were reviewed and appeared to be appropriate.

These more complex queries had a lot of aggregation within them, as well as a lot of case statements, so there was significant processing aside from the join. To address this, tempdb was loaded RAM. There was no improvement because of this.

the cpu usuage and memory usage on the new machine is very low (cpu below 10%, mem below 10%). The data was stored on raid 10. The disk controller was improved yesterday and we are still waiting to hear the results of this.

my feeling (wether it's right or wrong) is there is some sort of bottleneck.

Any thoughts or suggestions?

Jim

[skhanal]

Is the database version and OS version same?

How did you migrate your data?. Did you migrate all indexes together with the data?

[rmiao]

Check i/o counters in perfmon as well.

[Jim]

thanks for the replies,

the db version and the operating system are the same. a restore was used to populate the tables. subsequent to this, all of the indexes where 'refresehed'

we did check the io counters and it turns out that that there are a lot of page reads (maxing out at 100%) where the query is run.

[rmiao]

Then try rebuild index to defrag pages.

[skhanal]

Is the sql server configuration same, run sp_configure in both servers and compare the values.

New server may not have enough memory allocated.