July 18, 2010 at 10:27 pm
Hi,
I am facing an issue with the report running really slow after the migartion of the DB from SQL 2000 to SQL 2005.Cursors are being used for genarting the report.When running the reports againts the SQL 2K instance its hardly taking 30 seconds where as when ran against the new SQL 2005 instance its taking 22 minutes.
I checked for blockings while running the report but could not find any.The memory utilization seems to be fine (the Total server memory is always less than taget server memory).The buffer cash hit ratio is 99%.
Please find the sever configuration for the old and the new Servers
Old
Total server memory: 4 GB
ProcessorCount :4
Server : windows 2003 32 bit
new
Total server memory: 32 GB
ProcessorCount :8
Server : windows 2003 64 bit
I have set the compatibility to 80 on new sql 2005 instance.
I ran the traces while running the report against sql 2K and SQL 2005.When analysed the traces i found that the cursor statemnts used for genearting report are getting executed in no time on old sql 2K but they are taking some time when pointed to new SQL 2005 instance.This issue has been bugging us since quiet some time...Please some one help me..
July 20, 2010 at 7:55 am
1) what version of SQL 2005 are you on? did you fully patch it up before go-live?
2) did you update ALL statistics with FULL SCAN after the upgrade?
3) did you check the query plan(s) for the new system?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply