SQL Server Performance

upgrading sql2000 to 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sunil_vt, Apr 9, 2007.

  1. sunil_vt New Member

    hi,
    my company wants to upgrade sql2000 to 2005 and they asked me to make some changes in SP's can someone gudie me what are the tips and steps to upgrade a SP in 2005(like any keywords or changes in Order By clause).Please help me
  2. ranjitjain New Member

    I'm sure your 2000 SP will work in 2005 without any changes.
    In 2005, error handling has changed and many new clauses like output clause and new set based operations has been added like except,intersect.
    So you could try altering your SP for optimisation
  3. satya Moderator

    The best is to check with Upgrade advisor for the databases you want to upgrade, also chekc the changes on the compatibility issues using SQL server books online.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. sunil_vt New Member

    hi,
    thanks for repling me but the thing is that some SP's has to change, for better perfomance like a SP in 2000 using Nolock should use WITH Nolock in 2005, a Order By clause has b change if u find any plz reply me
  5. satya Moderator

    It is hard to say how best you can achieve the good performance when upgraded without looking at the code, I think it is not possible to post all of them here. As referred earlier many system functions and stored procedures supported in earlier versions of SQL Server will not work. Books Online for SQL Server 2005 has an index of all backward-compatibility issues, including database engine changes and deprecated commands. Commands that were marked as deprecated prior to SQL Server 2000 should not have been used in SQL Server 2000 anyway.

    The best option is to test the upgrade on the dev. environment and see how your application is controlled or managed after the upgrade, fyi look at this add-inhttp://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc.

    Using NOLOCK is deprecated in SQL 2005http://msdn2.microsoft.com/en-us/library/ms143729.aspx.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. MohammedU New Member

    SQL Server 2005 Books Online
    Backward Compatibility

    The following LINK contain backward compatibility information for Microsoft SQL Server components. This content includes information about deprecated features, discontinued features, breaking changes, and behavior changes.

    http://msdn2.microsoft.com/en-us/library/ms143232.aspx

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page