SQL Proc slower on newer/faster machine?

I have a fairly complex procedure that currently takes about 1:30 (min:sec) to complete. We are transitioning to a new server and when I run the same query against the same data on the new server it takes almost 2:30 to complete.

The old server is a single proc - dual core Intel 4 2.8 GHz CPU with 3 GIG of RAM running Windows 2000 and SQL 8. The new server is a dual proc - dual core (4 cores total) 3.4 GHz machine with 6 GIG of RAM running Windows 2008 R2 and SQL 2008.

I had hoped to see this procedure (which gets run frequently every hour) get faster with the new machine and new SQL but it is now over 50% slower?! I work for a small company doing all the programming and networking but am not a DBA. Is there pos开发者_如何学Pythonsibly some kind of optimization being done on the old machine/SQL that is not happening on the new machine/SQL? The new machine is doing nothing but running SQL which has been set to use all the RAM available.

When I run the procedure it only seems to use one processor core and all the RAM it can get (same thing on both machines). And it only appears to use the one processor about 50% on average.

Any guidance on what might be causing this slowdown is appreciated.

Brian

I assume you upgraded the database by doing a restore, did you update the statistics (with full scan) or rebuild all the indexes? Otherwise you won't get optimal plans

for a full scan, you can do this

EXEC sp_msForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'

This might take a while if you have large table

The probable issue is that your SQL Server has a bad query plan for the stored procedure.

Here are a few common methods for defeating the issue:

1. Optimize for Unknown - Tell your SQL Server that, for this stored procedure, it should always determine, on-the-fly, what query plan to utilize -- It will not use historical information. This will often result in more-consistent run-times.

2. Prevent Parameter Sniffing - Sometimes SQL Server takes a look at your parameters and pivots to a very particular query plan. Sometimes this is very undesirable and can take an otherwise very-fast SPROC and turn it into a multi-minute behemoth. By re-declaring and copying all of your parameters, this becomes a non-issue.

3. You should also clear your stats

   EXEC sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN'

If updating statistics and other in DB changes don't help, check the server itself.

Does the new server have just one drive and the old server is split across multiple drives?

Are the new hard drives slower slower than the old hard drives?

Is virus scanning enabled on the new server?

Look for other server config issues.