On my Dell vostro 3550 note book with Windows7 64 bit and 8GB memory everything locks up or goes really slow after restoring the sql 2014 db backup to active sql express 2014 instance.

Solution : In SQL,applications that are migrated from earlier versions of SQL Server should be carefully tested to confirm that good performance is maintained or improved. If performance degrades like our case, you can set the database compatibility level to 110 (Default compatibility-level setting is 120 for sql 2014) or earlier to use the older query optimizer methodology. So we can achieve the same by running the following query for the specified restored database.

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = 110;

Database_name : Is the name of the database to be modified.

COMPATIBILITY_LEVEL {80 | 90 | 100 | 110 | 120 }

Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

Featured image

For all installations of SQL Server 2014, the default compatibility level is 120. Databases created in SQL Server 2014 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2014 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 100. Upgrading a database with a compatibility level of 90 sets the database to compatibility level 100. This applies to both system and user databases. Use ALTER DATABASE to change the compatibility level of the database. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2014, convert the application to work properly. Then use ALTER DATABASE to change the compatibility level to 120. The new compatibility setting for a database takes effect when the database is next made current (whether as the default database on login or on being specified in a USE statement).

Compatibility-level setting of 120 (Default on sql 2014) :

SQL Server 2014 includes substantial improvements to the component that creates and optimized query plans. This new query optimizer feature is dependent upon use of the database compatibility level 120. New database applications should be developed using database compatibility level 120 to take advantage of these improvements. Applications that are migrated from earlier versions of SQL Server should be carefully tested to confirm that good performance is maintained or improved. If performance degrades, you can set the database compatibility level to 110 or earlier to use the older query optimizer methodology.

Database compatibility level 120 uses a new cardinality estimator that is tuned for modern data warehousing and OLTP workloads.

Reference : https://msdn.microsoft.com/en-us/library/bb510680.aspx