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?
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.
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.
18 People are following this question.
