Performance Hit after upgrading to SQL 2005

I am hit with bad performance after upgrading my sql 2000 to sql 2005 version. All the infrastructure remains expect the performance :(

Please provide any pointers /guidelines so that I will check the same.

Thanks in Advance

Varun

I am seeing a huge performance problem right now.

I have to tagged to the TempDB right now. It seems to be leaving leftover objects and making the TempDB a mess. Check yours for a bunch of "#" + 8 digit Hex

If I stop and restart my SQL Services, which redoes the TempDB....I am good to go for a day or 2...then the same old problem. I have called MS about this but I am waiting for response from them

select * from tempdb.dbo.sysobjects where name like '#%' order by crdate desc

|||

What is the problem with performance, is it a particular query?

Have you rebuilt your indexes? This is a must do excercise after upgrading to SQL 2005.

|||

I had the same problem -- we got a new server to run sql server 2005. It was a major upgrade over the old server. When we started testing our code on the new machine, we found that a significant amount of our code ran a lot slower on the new machine with sql 2005.

Eventually we were able to change our code, mostly through trial and error, to get the performance we expected. In almost every case, the performance problems were caused by:

1. Sub-queries - apparently the sql 2005 optimizer isn't as good as the sql 2000 optimizer at figuring out when the sub-query needs to be re-evaluated -- so large recordsets that join to a sub-query can be really slow since the sub-query sometimes gets re-evaluated for each record it's being joined to even when the sub-query generates the exact same result for each parent record.

2. Table variables - when we substituted #temp tables for table variables, performance increased dramatically -- in some cases putting a primary key on the table variable fixed the problem.

Unfortunately, we had to spend a couple of weeks to change all of our code, but eventually we were able to fix the performance problems.

Hope that helps.