SQL 2000 and SQL 2008 R2 Performance Comparison | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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!
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
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..
Red gate SQL monitor
Lepide Sql Storage manager
Quest Sql manager etc


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |