Upgrade - Compatibilty Level - Performance

• Paresh Randeria

  Ten Centuries

  Points: 1150

  More actions

  July 28, 2008 at 6:25 am

  #125319

  I have upgraded my database to SQL 2005 and done basic DBCC like checkdb/Update Statistics/UPDATEUSAGE.

  I have not change compatibility level and I understand that I can NOT use SQL 2005 New features unless I change level to 90.

  My question is related to Performance and only Performance.....

  My understanding is that even if I do not change the Compatibilty Level, I should get better performance as SQL 2005 Database Engine is smarter than SQL 2000....

  My question is:

  DO i get any performance gain by changing Compatibilty level to 90? OR

  My performance will be the same even if I do not change the compatibility level.......

• dmc-608719

  SSCarpal Tunnel

  Points: 4663

  More actions

  July 28, 2008 at 9:48 am

  #849252

  Good question. I have recently performed the same upgrade and switched the compatibility mode to 2K5. I have not seen any peformance gain or decrease. My system has more hardware than needed right now, so my results might not be typical.

  I would not that all Microsoft documentation indicates that leaving a database in lower compatibility mode is not recommended. It is only a hold over. So I would worry more about other things than performance, like corruption, lack of support going forward, etc.