SQL Server Performance

SQL 2000 and SQL 2008 R2 Performance Comparison

Discussion in 'ALL SQL SERVER QUESTIONS' started by Nhitz, Nov 18, 2012.

  1. Nhitz New Member


    We are in doing SQL migration from SQL 2000 to SQL 2008 R2 and one of the concerns is performance especially on those components (i.e., stored procedures, views or functions) that are running for a long time in SQL 2000 will not experience the same in SQL 2008 R2.

    We already did the following actions:
    First, compared the execution plan and the expectation was both 2000 and 2008 R2 results should be the same. However, there are instances that one object (bookmark lookup in 2000) is a nested lookup in 2008 R2.

    As there is not much explanation by Microsoft behind this, we proceeded to another approach. That is, compare the execution plan cost (CPU cost bet. 2000 and 2008 R2). As the comparison results were not much of a difference (see sample results from table below), we executed every stored procedure with Client Statistics set to on to compare the execution time.

    SQL 2000 SQL 2008
    Query 1 Select 0 Execute 0
    Bookmark Lookup 0.00005
    Index Seek 0.003283
    Query 2 Stored Procedure 0
    Seton 0
    Select 0
    Nested Loop 0.0000042
    Index Seek 0.0032831
    RID Lookup 0.0032831
    Total 0.003333 0.0065704

    Comparing again the execution time, the comparison results were not much difference (as Client Statistics result are only in milliseconds).

    Are there are other options where you can recommend to compare the performance results? Would appreciate your valuable recommendations/suggestions. Thanks!
  2. Shehap MVP, MCTS, MCITP SQL Server

    First, welcome to Forums

    Switch over from SQL Server 2000 to SQL Server 2008 doesn’t represent alone the main key of performance but still there are lots of new 2008 R2 techniques for SQL Server Performance Optimization such as Filtered index , Index Compression and Forceseek hint on columns …etc which you can learn more about them at my blog : http://www.sqlserver-performance-tu.../12927042-t-sql-performance-optimization-6-6-

    In addition , SQL Server 2008 ,R2 enjoys new rich power of T-SQL commands like Merge which can help you significantly in boosting the performance of DML queries such as insert, update and delete…

    Kindly try the above recommendations along with your T-SQL query and let me know if you need any further help
  3. peterdru401 New Member

    For performance comparison you can go for a specific tool which will monitor and analyze your sql server performance at specify periods or with in a specify period also
    it will generate a comprehensive report for the same ...
  4. Luis Martin Moderator

  5. peterdru401 New Member

    tools like..
    Red gate SQL monitor
    Lepide Sql Storage manager
    Quest Sql manager etc

Share This Page