By Melody Zacharias, ClearSight Solutions
I am sure you have heard by now about the new version of SQL Server, simply called SQL Server 2016. You may be wondering if the new version will affect your business as it pertains to both T24 and Temenos Analytics. In this blog, I have compiled my top 3 favorite changes that directly impact your workload and performance.
First let’s talk base speed. This release of SQL Server is the first time since version 2005 that Microsoft has made any significant change to an engine process. I don’t mean an add on or additional functionality, but an actual change to how SQL Server runs under the hood. Every action that SQL Server does has to be written to a disk, regardless of how you set up SQL Server, this is just how it functions. SQL does this by writing to a log file before any transaction is complete. In versions, up to 2016 this has always been done with a single log write worker. SQL Server 2016 has extended the log writer to allow up to 4 workers for each instance. According to the performance tests done by Microsoft, SQL Server 2016 will perform a minimum of a 15% faster. This increase in performance comes with the upgrade, and assumes no other changes are made. SQL Server 2016 truly does run faster. This multi threaded log writer is by far my favorite addition to this edition
Another functionality change that will make a difference is the addition of a few default behaviors that can significantly impact your performance on both T24 and Temenos Analytics. Trace Flags, that database professionals have used in the past to help speed up functionality, have been added as defaults. These seldom used features are now default settings that can improve performance on most systems.
Trace Flag 4199 - Controls query optimizer behaviors
Trace Flag 2371 – Changes threshold for the update of statistics
Trace Flag 1117,1118 – These control the growth and extension of TempDB files
My third favorite feature is a change to security. Banking can never be too secure and Microsoft is helping us out with this by upgrading the default endpoint encryption from RC4 to AES. This is a great improvement as AES is considered the gold standard by military and intelligence organizations around the world.
This is by no means an comprehensive list of the improvements available in SQL Server 2016. It is, however, a list of a few significant improvements that will require no additional work on your part in order to reap significant performance improvements.
If you are not ready to make the upgrade to SQL Server 2016, there are a few items updated in the latest version of SQL Server Management Studio (SSMS) that will help you improve the work flow of your older software. For example, there is a new query result grid option that supports keeping Carriage Return/Line Feed (newline characters) when copying or saving text from the results grid. This feature is very useful for copying data to other applications. This feature can be accessed through the Tools/Options menu. NOTE: The new SSMS will require you to install .NET 4.6.1 but this is only local to the machine you install SSMS on.
The Upgrade Advisor Tool is also “new and improved”. This Advisor enables you to run the upgrade rules against your current version to see where you may have issues or are using deprecated features. Also new to the Upgrade Advisor is that it will point out new features that you aren’t currently using but may want to take advantage of as well.
Microsoft has been hard at work making SQL Server both faster and easier to use. In the banking industry, keeping up with the technology curve is an imperative that your customers insist on. Let’s keep the customers happy by making things faster and easier with SQL Server 2016.
Melody Zacharias, Partner
Microsoft MVP – Data Platform
+1 (250) 231-5662