Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help, queries perform differently on 2005!

Status
Not open for further replies.

katbear

Programmer
Mar 14, 2007
270
US
I have 2 servers, one is 2000, the other is 2005, sp1. The databases are identical, because I am restoring the database from the 2000 server onto the 2005 server.

I am running some stored procs on both servers. They take about 30 seconds on 2000. However, on 2005, they are taking like over 15 minutes to run!!

This is crazy. Looking at the execution plans, they are MUCH more complicated on 2005, for the *exact* same stored procs.

The stored procedure themselves aren't even that complicated. Yes, they use inline functions, but so what. This difference in performance is shocking.

Any ideas??? I'm stumped.

Thanks
 
It's not an upgrade. It's a brand new installation of SQL Server 2005 on a much faster server.

 
what I meant was did you just backup and restore the DB from the old server to the new server?

Yes

also make sure that you have as many files for the tempdb as you have physical cores per CPU

??
 
Ok, I will give it a try.

Haven't seen this problem before, though, and I've restored the db many times w/o rebuilding any indexes.

Most of the procs run faster on 2005, except for these really slow ones.

Anyways, I have 100's of tables. Do you have any recommendations on how one should drop and recreate all these indexes?? That's going to be a lot of work, as some are primary keys, unique keys, or non-clustered indexes, and the syntax is different for each one.

I have a script that can rebuild the indexes, but not drop and recreate them.

 
Crud. I restored the db, dropped AND recreated all indexes for the tables that are used by the stored proc. I updated the statistics also.

NO DICE.

The queries run like mud on 2005.

And I was wrong about the service pack. We're on sp2.

I have NO idea why this is happening at this point. Has anyone else had this problem??
 
>>I have NO idea why this is happening at this point. Has anyone else had this problem??

what was the costliest item in the query plan? and how different are the 2 query plans???

Known is handfull, Unknown is worldfull
 
We eventually figured out the "problem" and rewrote some syntax.

However, there is still no explanation why the EXACT same query runs in 15 seconds vs. "never returns", on 2000 and 2005 respectively.

It seems that it locks up on 2005.
 
because the optimizer got rewritten so stuff that was slower might run faster now and vice versa

In most cases stuff runs faster, stuff with temp tables and a lot of correlated subqueries might run slower
rewriting it by using CTEs and 2005 functionality will speed it up because the optimized has been optimized for that

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Where can I find more info on this? CTEs I mean.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top