Is an Upgrade Useful?
Database performance is always an issue, especially when considering upgrades. Microsoft just provided a new version in 2014 of SQL Server and then another version in 2016. The 2014 version of SQL Server included some newer technology innovations that were long overdue and improved overall performance. Now the question could be raised what are the real performance differences between the 2014 and 2016 versions? Is an upgrade to the newest version of SQL Server worthwhile?
An IT manager might well sit down with database administrators and discuss these very issues. Technical terms, code, queries and much more are likely to come out of the conversation, many of which may well leave many managers without specific database experience confused on the issue. There are some basic performance factors which can be provided that may well assist many non-database experts in their understanding of performance issues and improvements.
One of the main factors to consider is that the SQL engine for the server version remained basically the same since 1998 when SQL version 7 was in use (specifics detailed below). With the 2014 version the engine for SQL Server was upgraded to take advantage of hardware technology improvements, so just having this version in use is a good move forward.
But with the 2016 version there were additional changes to the SQL Server engine which made its processing better integrated with current technology so that it provides far faster processing of database functions. Some observers in the industry have likened this to cars and local transportation needs. You can have a very fast car but unless the actual infrastructure is upgraded then traffic flow may be an issue regardless of how great the car may be. Microsoft has taken the approach of improving the SQL Server engine so that it takes full advantage of technology changes to hardware.
Likewise, when it comes to cloud-based database services, Microsoft introduced the service-based version of SQL Server 2016 to run natively in Azure. This means that all of the changes to the SQL Server engine are fully integrated on the Azure hardware platform and further means that a shift to cloud-based databases may well provide the best performance, especially for growing databases. Additionally, with newer versions of SQL in the future, databases will be much easier to upgrade from Azure for continued improvements to performance and integration.
Comparing Performance – 2014 vs 2016
- What should you be concerned most about when thinking of performance improvements with the latest version of SQL Server? For that answer we turn to what was changed with version 2014 which, as hinted at above, involved the server engine – specifically the cardinality estimator. Without getting into too many technical concerns, the cardinality estimator is basically the core of the query optimizer. Within this core, the cardinality estimator works with statistics within the database for best functionality. If the estimator grows less accurate, then queries are slower. The upgrade of the cardinality estimator in the 2014 version, which had not been upgraded since 1998, was a big step forward. In this regard, the 2014 version is a major improvement in performance over older versions.With the 2016 version of SQL Server, there are even more improvements to the functions of the cardinality estimator which provides even better performance. However, there may need to be testing with older databases to verify which compatibility version can run in this latest version of the SQL Server.When it comes to the compatibility levels that are available in the 2016 version you will find that if you must run an older compatibility level you may lose some of the latest feature functionalities. The good news is that if you must run the older version of the cardinality estimator there is a command line configuration which will allow you to run all of the latest features of the 2016 version with the older estimator.
- There are a few more features which can be very beneficial to migrating to this latest version of Microsoft’s SQL Server. Multi-threaded insert – select statements can now be used. Memory optimized tables can also now be multi-threaded.
- Additionally, statistics within the database are now updated more frequently which creates a higher level of estimates for better performance. Just as an example, previous behavior for a table of 1 billion rows would have been triggered for update after changes to 200 million changes in a database. With SQL Server 2016 these updates are now triggered at 1 million changes which creates more accurate estimates of statistics within the database for much faster queries.
- With relational databases, tables can now be linked to each other for improved data integrity using foreign key constraints. Previously there were limitations to the number of key constraints which could be used, that number being 253. With SQL Server 2016, foreign key constraints now have a limitation of 10,000. For a relational database, this means that thousands of tables can now reference to a single user table and take advantage of data integrity in a much broader range. It is important to note that this is for relational databases only.
- The importance of the foreign key constraint changes and, as mentioned above, the number was limited with SQL Server version 2014 due to the extensive computing cost. However, Microsoft has reduced the cost to resources and increased the number of foreign key constraints by introducing a new version of the referential integrity operator. Again, without being too technical, the referential integrity checks within a database are now done within the new query execution operator. The result is much faster performance for the internal workings of databases running on SQL Server 2016.
These are just some basic points of performance improvements Microsoft has made between versions 2014 and 2016. For a more in depth understanding of what these and other features in the latest version may mean for your database performance, you may need to consult with experts who can examine and explain more of what may be needed in consideration of migrating to SQL Server 2016 to provide the best experience to clients, customers and users of your database offerings.
Conclusion
It may be confusing to determine how to apply IT budgets when it comes to various upgrades, especially when considering those for databases. However, it is important to keep in mind that as databases grow in size they may not just outgrow storage availability but also processing capabilities.
Keeping current with the latest versions will likely provide many benefits to existing databases which have already been upgraded. There may be additional factors to consider as to whether these databases are working fully integrated with the most current versions of SQL Server. In this case, it can be highly beneficial to consult with professionals who can adequately determine current health, configuration, and modes in which a database may be operating. A full investigation of organizational databases will be increasingly important to determining how performance in the latest version of Microsoft SQL Server can be improved as well as maintained for upcoming versions and possible migration to cloud-based services.
To learn more about Microsoft SQL Server, contact our experts at RoyalDiscount.com– your online source for cheap OEM, Retail & Cloud products.