From what I understand, you have upgraded from SQL Server 2000 to SQL Server 2008 on a test server and hoping the performance
will improve dramatically?
I am afraid, it doesn’t happen like that automatically.
Suhas asked you to run the production load at-least a day but you have replied in less than 4 hours and am NOT sure how much
time did you spend actually running the workload.
Coming to the processes being in SUSPEND state, after the upgrade to SQL Server 2008, have you performed any post upgrade tasks
like rebuilding the indexes or updating the statistics of all tables/columns?
If you haven’t, then download this MSFT paper and glance over for the steps.
Also, is your test server (including IO subsytem) comparable to the production server? In most companies those won’t be
anywhere near comparable. Then how are you measuring the performance?
Also look at WAIT STATS on test server to see what are the bottlenecks?