Reports (using cursors)running slow after migration to sql 2005

Question

Post reply

Reports (using cursors)running slow after migration to sql 2005

mahadasu_ravichaitanya

SSC Veteran

Points: 229
More actions
July 18, 2010 at 10:34 pm

#223412

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic954585-65-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 2

Since u have migrated the database, You should do rebuilding indexes as well as Update the statistics. This will definitely help in reducing response time.

mahadasu_ravichaitanya SSC Veteran Points: 229 More actions · Answer 3

Yes I did rebuild the Indexes...ran update usage,update statistics....but still the issue remains 🙁

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 4

Check execution plan for cursor-qry as well as the queries participating inside the cursor structure... that might help......

theHeimes SSC Rookie Points: 49 More actions · Answer 5

Generally, using cursors is slowing down performance.

As fas as reports are concerned I'd assume that you can rebuild the query to a set-operation.

It might help, if you'd post the code.

sk-458789 SSC Enthusiast Points: 188 More actions · Answer 6

Please post the cursor query to help you better.

SKC

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

compare the query execution plans of the cursors from your old and new...im sure there should be some difference in the plan...

MMartin1 One Orange Chip Points: 27794 More actions · Answer 8

Also if you haven't done so, try having the reports call a stored procedure and pass paramaters as opposed to building the query in the report. THis reduces network traffic and caches the query on the server.

----------------------------------------------------