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..