SQL 2000 and SQL 2008 R2 Performance Comparison

Hi,

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!

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

tools like..

nagios

Red gate SQL monitor

Lepide Sql Storage manager

Quest Sql manager etc