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

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

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

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

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

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

  • Please post the cursor query to help you better.

    SKC

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

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

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

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

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