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!

Register Log in
  • Congratulations strongm 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

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
 
Sort by date Sort by votes
It's not an upgrade. It's a brand new installation of SQL Server 2005 on a much faster server.

 
Upvote 0 Downvote
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

??
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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??
 
Upvote 0 Downvote
>>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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Where can I find more info on this? CTEs I mean.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
192
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
488
Chris Miller
Chris Miller
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
328
pjw001
pjw001
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
75
bmacbmac
bmacbmac
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
219
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top