Post reply

My application's response time has reduced form nearly 20 sec to around 2 sec after upgrading from SQL Server 2000 to SQL Server 2005.

  • November 18, 2008 at 5:12 am

    #124318

    Hi,

    My application's response time has reduced form nearly 20 sec to around 2 sec after upgrading from SQL Server 2000 to SQL Server 2005.

    Could anyone help me how such a big difference in perfomance is seen.

    I monitored that full scans/sec has gone up from 1.5 per/sec to 230 per sec with SQL Server 2005.

    Is there any settings in SQL Server 2000 to make it perform faster as SQL Server 2005 works.

  • November 18, 2008 at 5:36 am

    #899826

    First thing that comes to my mind is the improved database engine 😀

  • November 18, 2008 at 5:58 am

    #899844

    sandeephughes (11/18/2008)

    Could anyone help me how such a big difference in perfomance is seen.

    just to be sure... you did an upgrade in place, didn't you? meaning, neither moved to a different box nor upgrade process included any storage or object reorganization; is my assumption correct?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • November 18, 2008 at 6:12 am

    #899855

    Hi,

    Thanks for your reply.

    I just taken backup of database with SQl Server 2000 server and saved it.

    Then uninstalled SQL Server 2000, and installed SQL Server 2005. After that restored the backup. and performed performance testing with SQl Server 2005 with restored database and seen dramatic improvement in response time of application.

    --Sandeep

  • November 18, 2008 at 6:14 am

    #899858

    I have kept the hardware environment same for sql server 2000 and sql server 2005.

  • November 18, 2008 at 8:01 am

    #899941

    I've often wondered (but not hard enough to actually lift my fingers and go reading on my own) if doing a backup, drop DB, restore sequence gave you a completely ordered, non fragmented database and /or set of indexes.

    If so, you might have simply accomplished through the "upgrade" what you should normally be accomplishing by maintenance.


    Student of SQL and Golf, Master of Neither

  • November 18, 2008 at 11:54 pm

    #900350

    I don't think you are likely to see improvements of this magnitude by performing database maintenance. When we moved from SQL 7 to SQL 2000, we had a process that took 40 minutes start taking 3 minutes. Improvements in the database engine can have huge impacts on performance of certain processes.

  • November 19, 2008 at 3:28 am

    #900404

    Ross McMicken (11/18/2008)

    I don't think you are likely to see improvements of this magnitude by performing database maintenance.

    I have to disagree. Performance in a non properly maintained database keeps deteriorating until it just sucks.

    Having said that I would say you are seeing performance improvement because of implicit database maintenance during the upgrade process and also because of a much better SQL Server engine on v2005.

    How much of the improvement is caused for each one of the above mentioned items heavily depends on how negleted your database was in terms of maintenance.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • November 19, 2008 at 3:34 am

    #900406

    Hi Paul,

    If i do take restore the backup to SQL Server 2000, then also its working very slow. response time is coming 20 sec. i tried everything on 2000, but still response time is coming very slow. running the DB on SQL Server 2005 solve performance problems.

    I dont think Maintenance has any role to play in my case.

    I am just running my performance test for 3 hrs with every DB restore and restart of machine.

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

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