I am in the process of converting a 6.5 database to 7.0. I have a new server and a clean install of 7.0 with sp2. I decided to build the new database
from scripts instead of using the upgrade wizard. My problem is performance. One stored procedure runs on my old box in about 50 minutes. The
same procedure on the new box takes between 5 and 8 hours. The biggest difference is the 6.5 database had tempdb configured to run in
RAM (600mb).
The stored procedure has two cursors which process over a million rows in one cursor and over 300,000 rows in the other cursor. The procedure is
doing a string comparison and either an insert or an update based on the outcome of the comparison.

I have tried setting the memory to a fixed amount rather than having it dynamically configure. (The machine has 2gb on RAM, I am using all but the necessary
amount left for NT) I have tried changing the cursor threshold to run asynchronously instead of synchronously.
(this added time to the process). I have double checked the indexes. I am not sure what else to try. The other problem is after the process runs,
SQL does not appear to be releasing the memory (both when configure dynamically and when configured as fixed) I am using process monitor and
task manager to keep track of the amount of memory being consumed. After the process runs, the memory for SQL is still running at 100% in process
monitor and 1349820 k in task manager.
Any advice would be greatly appreciated!!!
Thanks
Trina Blazek