locked
Migrate Microsoft SQL Server 2000 to 2005 RRS feed

  • Question

  • I just migrate our database from SQL 2000 to 2005. But after migration the application performance getting slow. Kindly advice how to make the database perform better.  
    Wednesday, October 19, 2011 6:49 AM

Answers

  • Hi SusantyLiga,

    The performance issue is related to the indexes and statistics which are not appropriate on the new environment. You may need to rebuild the indexes and update the statistics afterwards. Please perform the tasks on an off-peak time.

    For more issues about upgrading SQL Server 2000 to SQL Server 2005, please have a look at this blog: Upgrading to SQL Server 2005 FAQs.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Stephanie Lv Wednesday, November 2, 2011 5:15 AM
    Friday, October 21, 2011 8:43 AM

All replies

  • Hi

    Have you Run DBCC Update Statistics on all the tables/Indexes after the upgrade?

    After migrating to SQL2005 all statistics become unvalid and you should run DBCC update statistics


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    Wednesday, October 19, 2011 8:14 AM
  • Hi SusantyLiga,

    The performance issue is related to the indexes and statistics which are not appropriate on the new environment. You may need to rebuild the indexes and update the statistics afterwards. Please perform the tasks on an off-peak time.

    For more issues about upgrading SQL Server 2000 to SQL Server 2005, please have a look at this blog: Upgrading to SQL Server 2005 FAQs.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Stephanie Lv Wednesday, November 2, 2011 5:15 AM
    Friday, October 21, 2011 8:43 AM
  • Hi SusantyLiga,

    Reindex all dabase and Update statistics isn't enough, Unfortunally you must create a profiler over all user transactions and evaluate sql sentences and execute DTA tasks, because the engine of sql server 2005 is more complex and many sql statements are executed more  slower  than  in sql 2000 

    Now , maybe You don't need  create a new indexes, it's probably that You use a new capability of sql server 2005 and You only need modified indexes adding include columns

    Regards,

     

    Carlos A.



    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.
    Monday, October 24, 2011 8:13 PM
  • As well as the Update Stats, you will need to run an Update Usage. - http://msdn.microsoft.com/en-us/library/ms188414.aspx

    I hope this helps


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Wednesday, October 26, 2011 11:46 PM