After SQL 2008 Upgrade Query 100% CPU

Hi Everyone,
   I upgrade our SQL 2000 cluster to SQL 2008. The process seems to have gone smoothly. We are noticing one problem though
   We have a tomcat website query the database to display results and so forth. A couple of queries that used to run quickly and display the data now cause the CPU to spike all the way up to 100%, especially if multiple people are trying to use the page and requesting the data.
   Has anyone run across this situation as to why a query would take so much longer and eat so much CPU when it was never an issue in SQL 2000? I have tried this on a test system and have rebuilt my indexes and even installed the latest cumulative update. Also i have tried turning CPU paralleling off and changing the max worker threads to 0 to allow sql engine to decide how many.
   The sql server is on windows server 2003 sp2 with (2) quad core processors and 4GB of ram.

Thanks for any insight.
eric121899Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you update the statistics?
did you also change the database compatibility from 80 to 100 ?
0
eric121899Author Commented:
I have updated the stats on the production system right after the upgrade. On my test system  I can reproduce the issue and have set compatibility to 100, though I believe the compatibility shouldn't effect performance..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you post the explain plan of the query(ies) that show the problem?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gothamiteCommented:
what is the setting of max degree of parallelism?

if you have eight cores you might want to experiment with values from 0 to 4
0
eric121899Author Commented:
The parallelism is set at 0 right now. I turned it to 1 to turn it off as a test but that didn't help.
0
eric121899Author Commented:
My execution plan has a query cost of 100%

Hash Match (Aggregate) 57%

Clustered Index Scan (Clustered) 43%

I know this is not good and I didn't develop the queries. I just find it odd that if it was still returning results at a fast enough pace in 2000 but not now.
0
eric121899Author Commented:
Update:

  It appears between 2008 and 2000 that the execution plans are very different. SQL 2000 execution plans uses parallelism/repartition streams. In SQL 2008 I don't see any mention of parallelism being used on the execution plan. It seems both servers have the same parallelism options set and so forth. Any idea if I can force 2008 to use a parallel execution plan? I have tried adjusting the threshold but that didn't help.

Thanks!
0
eric121899Author Commented:
Update:

   I believe the problem has been resolved. It would not use parallel because of a "IS NULL" statement in the query which is considered scalar and it not allowed for parallelism use. Kind of odd it was allowed in 2000 but not 2008
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.