cnewgard
asked on
SQL 2005 to 2008 SSRS CPU Spikes
We recently migrated from SQL 2005 to SQL 2008. Our SSRS reports worked fine in 2005. Now that we are in 2008, when a report is run from a single user the CPU spikes to 100% and often the report ends up timing out. We copied the database from our 2005 envirionment to our new 2008 environment. We rebuilt the stats and indexes.
Help Experts.
Help Experts.
ASKER
All of our reports max the CPU. I ended up installing an instance of SQL 2005 on the same box. I moved my reports there and things are looking better. I will check the execution plan of each and post my findings.
ASKER
Also, there are no complexities in the query. No linked servers. All datasets from the report pull from a single reporting database on the server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Expert was correct. Execution plans were different. The reports were also slow to run on 2005, just slower on 2008. We decided to revamp our reporting and optimize it for 2008. Thanks for all of your help!
If it's just this one report, it's possible that the database engine is making a different index choice in 2008 than it was in 2005. Can you post the query it's using? Any remote linked servers or other complexity in th query, or is it pretty standard stuff?
Also, do you still have a 2005 SSRS instance around somewhere that you could use the test this report? If you do, then you may be able to compare the execution plan to see what's different since your upgrade.