SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.
When I look at many SQL Server instances in the wild, I still see a large percentage of instances that are running extremely old builds of SQL Server for whatever major of version of SQL Server is installed. This is despite years of cajoling and campaigning by myself and many others (such as Aaron Bertrand), and an official guidance change by Microsoft (where they now recommend ongoing, proactive installation of Service Packs and Cumulative Updates as they become available).
Microsoft has a helpful KB article for all versions of SQL Server that explains how to find and download the latest build of SQL Server for each major version:
Where to find information about the latest SQL Server builds
Here is my commentary on where you should try to be for each major recent version of SQL Server:
SQL Server 2017
SQL Server 2017 and newer will use the “Modern Servicing Model”, which does away with Service Packs. Instead, Microsoft will release Cumulative Updates (CU) using a new schedule of one every month for the first year after release, and then one every quarter for next four years after that.
Not only does Microsoft correct product defects in CUs, they also very frequently release new features and other product improvements in CUs. Given that, you should really try to be on the latest CU as soon as you are able to properly test and deploy it.
SQL Server 2017 Build Versions
Performance and Stability Fixes in SQL Server 2017 CU Builds
SQL Server 2016
SQL Server 2016 and older use the older “incremental servicing model”, where each new Service Pack is a new baseline (or branch) that has it’s own Cumulative Updates that are released every eight weeks. Microsoft corrects product defects in both Service Packs and in CUs, and they also very frequently release new features and other product improvements in both CUs and Service Packs.
As a special bonus, Microsoft has also gotten into the very welcome habit of actually backporting some features and improvements from newer versions of SQL Server into Service Packs for older versions of SQL Server. The latest example of this was SQL Server 2016 Service Pack 2 which has a number of improvements backported from SQL Server 2017.
SQL Server 2016 Build Versions
Performance and Stability Related Fixes in Post-SQL Server 2016 SP1 Builds
Performance and Stability Related Fixes in Post-SQL Server 2016 SP2 Builds
SQL Server 2014
SQL Server 2014 will fall out of Mainstream Support from Microsoft on July 9, 2019. If you are running SQL Server 2014, you really should be on at least SQL Server 2014 SP2 (which got many improvements backported from SQL Server 2016), and ideally, you should be on the latest SP2 Cumulative Update. You should also be on the lookout for SQL Server 2014 SP3 which is due to be released sometime in 2018, which is very likely to have even more backported improvements.
If you are on SQL Server 2014 or SQL Server 2012, Microsoft has a very useful KB article that covers recommended updates and configuration options for high performance workloads. A number of these configuration options are already included if you are on the latest SP or newer for either SQL Server 2012 or SQL Server 2014.
SQL Server 2014 Build Versions
Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds
Hidden Performance and Manageability Improvements in SQL Server 2012/2014
SQL Server 2012 fell out of Mainstream Support from Microsoft on July 11, 2017. If you are running SQL Server 2012, you really should be on SQL Server 2012 SP4, ideally with the Spectre/Meltdown security update applied on top of SP4. Similar to SQL Server 2014 SP2, SQL Server 2014 SP4 also included a number of product improvements that were backported from SQL Server 2016.
SQL Server 2012 SP3 build versions
Performance and Stability Related Fixes in Post-SQL Server 2012 SP3 Builds
So just to recap, here are my recommendations by major version:
SQL Server 2017: Latest CU as soon as you can test and deploy
SQL Server 2016: Latest SP and CU as soon as you can test and deploy. Try to at least be on SQL Server 2016 SP2.
SQL Server 2014: Latest SP and CU as soon as you can test and deploy. Try to at least be on SQL Server 2014 SP2 (and SP3 when it is released).
SQL Server 2012: SP4 plus the security hotfix for Spectre/Meltdown.