eric121899
asked on
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.
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.
ASKER
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..
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
can you post the explain plan of the query(ies) that show the problem?
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
if you have eight cores you might want to experiment with values from 0 to 4
ASKER
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.
ASKER
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.
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.
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
did you also change the database compatibility from 80 to 100 ?