question

cornpoppy avatar image
cornpoppy asked

I installed sql server version 2014 but my database compatibility level is 100,is it Poor Performance

Hi I installed sql server version 2014 but my database compatibility level is 100,is it Poor Performance?
dbasql server 2014dba-developercompatibility-level
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
sjimmo avatar image
sjimmo answered
Essentially the compatibility level of a database allows for a partial backward compatibility of that database to the version of SQL Server it is set for. In this case 2008. This setting should not have an impact on performance. But you might consider, especially if this is an upgrade from a previous version which it looks like it may be, to do a dbcc checkdb() with no_infomsgs and rebuild the indexes.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There are some instances where a query will get a poorly performing plan when compared to a previous version of SQL. That is an inherent risk in any SQL version upgrade and you should always plan to spend at least a little time post-upgrade tuning a handful of queries that no longer perform the way they did in the previous version (usually, but not always mitigated by reverting the compatibility level). Another thing that often catches people out is when they relied on an implicit sort order in their resultsets that happened to change after the upgrade. To add to what @sjimmo mentions on the DBCC side, I always throw in a DBCC UPDATEUSAGE() when migrating a database, as well. Another thing to note is that, if the database was originally created in a version of SQL prior to 2008, at least one DBCC CHECKDB() should include "with data_purity" if it has never been run explicitly. That will enable the data_purity checks to run implicitly going forward. Hundreds of unrelated little things like that, but nothing with the compatibility level that would definitely cause poor performance.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.