Performance Enhancements in SQL Server 2014

SQLServerF1

There has been few new versions of SQL Server released of recent which include SQL Server 2012 and the latest being SQL Server 2014. There are many new features introduced with SQL Server 2014 and there has been many enhancements to the existing features of database engine, SSRS, SSIS and SSAS. Below are list of some of the important Performance Enhancements in SQL Server 2014.

The most important and most talked feature in SQL Server 2014 is In-memory OLTP, which is a new lock-free, latch-free, optimistic concurrency approach of storing the user data in memory. In some specific scenarios the improvement in performance and/or throughput can be about 40x. Another important feature is Transactions with Delayed Durability. A delayed durable transaction returns control to the client before the transaction log record is written to disk which may increase the transaction performance significantly. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

Other enhancements include support of up to 640 cores and 4 TB of RAM on a physical box, however this limit may be lower in a virtual machine, due to operating system and Hyper-V limitations, not SQL Server itself.
Another enhancement is reduction of eager writes to tempdb. The database engine will try to defer or completely avoid any physical writes of data that it assumes will be transient and short-lived. This means less writes in tempdb. There have been significant Improvements to the SQL Server Query Optimizer which include improvement of the component of the database engine that creates and optimizes query plans.
Another improvement include support of clustered columnstore indexes, also many of the data type limitations have been removed, and the index is now writable. New archival compression algorithm, with a much greater compression rate at a slightly higher CPU cost though.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Reasons to upgrade to SQL Server 2014

SQLServerF1

There has been quite few new SQL Server versions released frequently since the launch of SQL Server 2008. Currently the most used versions in most of the organizations are SQL Server 2005, SQL Server 2008 R2 and BDAs or organizations have been planning to upgrade from these versions to next higher version SQL Server 2012, however there has been another new release which is SQL Server 2014. Now there could be confusion about upgrading to which version? should we upgrade from SQL Server 2005/2008 R2 to SQL Server 2012 or 2014?

If you are planning to upgrade to higher version, then it is advisable to go with the latest release of SQL Server 2014, by skipping SQL Server 2012, as you can avoid use this version for longer time and there will be no need for another upgrade in few years, instead we can run with SQL Server 2014 for many years. Along with this there are also few other things which will prove to go ahead with SQL Server 2014 instead of SQL Server 2012, which include the license cost, which is almost same for both SQL Server 2012 and SQL Server 2014. Another important thing is that the end of life cycle date is far to ahead, so there is no force to upgrade to higher versions atleast for next 10+ years, unless we want to use any particular latest feature.

By upgrading directly to SQL Server 2014, you can get chance to use new features of SQL Server 2014 like In-Memory-OLTP, etc and also there are many enhancements to features like AlwaysON and also there are many enhancements to BI technology like SSAS, SSRS and SSIS. So, if you are planning to upgrade, it is better to prefer to go ahead with the latest releases always and in this case it is SQL Server 2014.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Steps for Side-By-Side Migrate or Upgrade plan for SQL Server 2014

SQLServerF1

SQL Server legacy versions are no more fully supported by Microsoft. Most of the organizations have already migrated to the latest versions of SQL Server long back, however there are few servers or applications which still use older versions of SQL Server like SQL Server 2000 SQL Server 2005 due to compatibility issues with latest versions of SQL Server versions. There may be lot of cost involved to upgrade legacy applications to work with latest versions of SQL Server, but it is high time to either move to another application or upgrade the application to work with latest SQL Server versions. There are two different methods available to migrate or upgrade an SQL Server instance to SQL Server 2014, which are In-Place Upgrade and Side-by-Side upgrade. It is always better to prefer Side-by-Side upgrade which is more safe method and also we can move to new hardware instead of using the old hardware to run the new SQL Server instances. Below are the important steps to keep in mind while upgrading from SQL Server 2005 or SQL Server 2008 R2 or SQL Server 2012 to SQL Server 2014 instance using side-by-side method.

– As a first step it is important to understand which objects would be affected after upgrading to SQL Server 2014. We can get this information by running the SQL Server 2014 Upgrade Advisor against the SQL Server 2005 or SQL Server 2008 R2 or SQL Server 2012 instance which will generate an report with list of any warnings or issues.
– As part of the up-gradation process, stop all write activity to the SQL Server 2005 or 2008 R2 or 2012 instance. This may involve disconnecting all users or forcing applications to read-only activity.
– Transfer data from the legacy instance to the SQL Server 2014 instance. This can be done by backup/restore of databases from SQL Server 2005 or SQL Server 2008 R2 or SQL Server 2012 to SQL Server 2014. To reduce the amount of downtime, one can look into options like Log Shipping or Database Mirroring or AlwaysON Availability Groups.
– Create supporting objects in system databases like SQL Server Agent jobs, security settings(Logins, Server role permissions), Instance level configuration settings, Database options and DTS packages (legacy mode) to the new SQL Server 2014 instance.
– Create new maintenance plans to mimic the old maintenance plans on server.
– Run validation scripts to verify integrity of new data (rebuild indexes, checkdb ..etc).
– Test the new instance to verify that applications work without any problems.

Also there are some new features available in SQL Server 2014 like In-Memory OLTP, Advanced AlwaysON Availability Groups, Advanced BI, etc, so check if any of the new features are worth to be implemented to High Availability, Security, Stability, etc.

Also, it is not possible to directly upgrading from SQL Server 2000 to SQL Server 2014, so in that case, first upgrade needs to be performed from SQL Server 2000 to SQL Server 2005 or SQL Server 2008 R2 and then to SQL Server 2014.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.