4.21a to 6.5 - Slow Stored procedures

[Geoff Oliphant]

We've just done an upgrade of our databases from 4.21a to 6.5 (sp3) onto a new box. The database came across fine and we haven't had any problems...except....

We have a nightly process that imports information from another system that bcps the data into some import tables and then we run some stored procedures (7 actually - darn 64k limit) to do some referential integrity checks before loading the new data into the existing production tables. The problem is that the stored procedures takes 90 minutes to run whereas on the 4.21a database it took under 30 minutes!

SQL Server 6.5 is on a much bigger box (RAID 5 with 5x4Gb disks) with 512Mb RAM but it just proves the old adage that more memory is not always better.

Could this just be a performance tuning issue or am I missing something here?

Thanks in advance

Geoff

[Ray Miao]

On 11/15/98 11:10:48 PM, Geoff Oliphant wrote:
> We've just done an upgrade of our databases from 4.21a to 6.5 (sp3)
> onto a new box. The database came across fine and we haven't had any
> problems...except....

We have a nightly process that imports information
> from another system that bcps the data into some import tables and then we
> run some stored procedures (7 actually - darn 64k limit) to do some
> referential integrity checks before loading the new data into the existing
> production tables. The problem is that the stored procedures takes 90
> minutes to run whereas on the 4.21a database it took under 30
> minutes!

SQL Server 6.5 is on a much bigger box (RAID 5 with 5x4Gb
> disks) with 512Mb RAM but it just proves the old adage that more memory is
> not always better.

Could this just be a performance tuning issue or am I
> missing something here?

Thanks in advance

Geoff

How many ram you give to SQL? By default, SQL server only takes 16mb ram.