SQL Server 2016 Service Pack 1 CU2 Released

On March 20, 2017, Microsoft released SQL Server 2016 Service Pack 1 CU2, which is Build 13.0.4422.0. This CU has 101 fixes in the public fix list, by my count. This is a pretty large CU, and if you look at the fix list in more detail, many of them are for pretty significant issues with AGs, columnstore indexes, and general performance.

Here are many of the more interesting Engine fixes:

FIX: Incremental statistics runs with higher sample rate than regular statistics when statistics are created or updated in SQL Server 2014 or 2016

Statistics are removed after rebuilding a specific partition of an partitioned aligned index on a partitioned table in SQL Server

FIX: On failover, the new secondary replica stops accepting transaction log records until the instance is restarted in SQL Server

FIX: Deadlock when you execute a query plan with a nested loop join in batch mode in SQL Server 2014 or 2016

FIX: “Non-yielding Scheduler” condition when you parallel-load data into a columnstore index in SQL Server 2016

FIX: DBCC CHECKFILEGROUP reports false inconsistency error 5283 on a database that contains a partitioned table in SQL Server

FIX: A memory leak occurs when SQL Server procedure cache consumes too much memory

FIX: Assert memory dump on a mirror server in SQL Server

FIX: Error 5262 when you execute DBCC CHECKDB on the primary replica in SQL Server 2012, 2014 or 2016

FIX: Availability databases in incorrect initializing/synchronizing state after failover of SQL Server 2014 or 2016 AlwaysOn availability group

FIX: More CPU consumption when many consecutive transactions insert data into a temp table in SQL Server 2016 than in SQL Server 2014

Improvement: Improves the query performance for SQL Server 2016 by changing the use of histograms on UNIQUE columns

FIX: Memory is paged out when columnstore index query consumes lots of memory in SQL Server 2014 or 2016

FIX: Queries that run against secondary databases always get recompiled in SQL Server

FIX: Out-of-memory errors when you execute DBCC CHECKDB on database that contains columnstore indexes in SQL Server 2014 or 2016

FIX: Significantly increased PAGELATCH_EX contentions in sys.sysobjvalues in SQL Server 2016

FIX: No automatic failover after database mirroring stops unexpectedly in SQL Server

FIX: TDE encrypted Databases go in suspect state during the recovery phase when you restart SQL Server

 

If you are running SQL Server 2016, you should be on the Service Pack 1 branch as soon as possible. If you are on the SP1 branch, then you should make an effort to be on the latest Cumulative Update as soon as you are able to complete the planning, testing and deployment process. Nearly a year ago, Microsoft changed their guidance about deploying CUs. Since then, Microsoft recommends ongoing, proactive installation of CUs as they become available.

 

 

Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds

As of February 21, 2017, there have been four Cumulative Updates (CU) for the Service Pack 2 branch of SQL Server 2014. There have been a large number of hotfixes in each of these cumulative updates. If you are running on the SQL Server 2014 SP2 branch, I really think you should be running the latest SQL Server 2014 SP2 Cumulative Update. Right now, that means SP2, CU4 (Build 12.0.5540), which was released on February 21, 2017. 

Table 1 shows the SQL Server 2014 SP2 CU builds that have been released so far.

Build Description Release Date
12.0.5511 SP2 CU1 August 25, 2016
12.0.5522 SP2 CU2 October 17, 2016
12.0.5538 SP2 CU3 December 19, 2016
12.0.5540 SP2 CU4 February 21, 2017

Table 1: SQL Server 2014 SP2 CU Builds

You can follow the KB article link below to see all of the CU builds for the SQL Server 2014 RTM, SQL Server 2014 SP1, and SQL Server 2014 SP2 branches.

SQL Server 2014 Build Versions

Like I have done for other versions and branches of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates in the RTM branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing is completely arbitrary on my part. You may come up with a completely different list, based on what specific SQL Server 2014 features you are using.

Here are the fixes in the SP2 branch:

SQL Server 2014 SP2 Cumulative Update 1 (Build 12.0.5511), 39 total public hot fixes

FIX: SQL Server crashes when you run a remote query in a stored procedure by using an invalid user name

FIX: Incorrect results when you use a LIKE operator and an “ss” wildcard in SQL Server 2014 or 2016

FIX: Memory leak on the AlwaysOn secondary replica when change tracking is enabled in SQL Server

“The log backup chain is broken” error when the log backup process fails in SQL Server

FIX: Error 1478 when you add a database back to the AlwaysOn availability group in SQL Server

Transaction log backup failure on the secondary replica prevents subsequent backups on Availability Replicas in SQL Server

FIX: Service Broker UCS task leaks memory in SQL Server 2014

Decrease in performance and “non-yielding scheduler” errors caused by unnecessary spinlocks in SQL Server

Operating system error 32 when you restore a database in SQL Server 2014

 

SQL Server 2014 SP2 Cumulative Update 2 (Build 12.0.5522), 17 total public hot fixes

Improved SQL Server stability and concurrent query execution for some columnstore queries in SQL Server 2014 and 2016

SQL Server 2012 crashes with an access violation when you use the TRY…CATCH construct for bulk copy

FIX: Queries that use CHANGETABLE use much more CPU after you install SQL Server 2014 SP1 CU6

FIX: A deadlock occurs when you execute a query plan that has a nested loop join between two hash joins working in the batch mode in SQL Server 2014

FIX: Cannot uninstall service packs for SQL Server 2014 after a cumulative update is installed

A memory leak occurs when you use Azure Storage in SQL Server 2014 or 2016

FIX: Access violation when you run a query that uses clustered columnstore index with trace flag 2389, 2390, or 4139

 

SQL Server 2014 SP2 Cumulative Update 3 (Build 12.0.5538), 37 total public hot fixes

FIX: Deadlock causes deferred transaction on the secondary replica in an Always On environment

A memory leak occurs when you use Azure Storage in SQL Server 2014 or 2016

Unexpected growth of tempdb data files when using SQL Server Service Broker

FIX: TDE encrypted Databases go in suspect state during the recovery phase when you restart SQL Server 2012 or 2014

FIX: On failover, the new secondary replica stops accepting transaction log records until the instance is restarted in SQL Server

FIX: An assertion occurs when you restore a clone database that has Query Store enabled in SQL Server 2014

FIX: DBCC CHECKFILEGROUP reports false inconsistency error 5283 on a database that contains a partitioned table in SQL Server

FIX: Availability databases in incorrect initializing/synchronizing state after failover of SQL Server 2014 AlwaysOn availability group

Statistics are removed after rebuilding a specific partition of an partitioned aligned index on a partitioned table in SQL Server

Updates to DBCC CLONEDATABASE functionality in SQL Server 2014

FIX: You cannot select any replica when you fail over from an availability group that’s in the resolving state

FIX: Out-of-memory errors when you execute DBCC CHECKDB on database that contains columnstore indexes in SQL Server 2014

FIX: Rebuilding a nonclustered index to add columns by using CREATE INDEX with DROP_EXISTING=ON and ONLINE=ON causes blocking

FIX: Queries that run against secondary databases always get recompiled in SQL Server

FIX: Distribution Agent fails for a SQL Server 2014 publisher and a SQL Server 2012 subscriber in Transactional Replication

FIX: The Target Recovery Time of a database set to a nonzero value causes an assertion and a lease timeout in SQL Server 2014

FIX: No automatic failover after database mirroring stops unexpectedly in SQL Server 2012 or 2014

FIX: Intra-query deadlock when values are inserted into a partitioned clustered columnstore index in SQL Server 2014

 

SQL Server 2014 SP2 Cumulative Update 4 (Build 12.0.5540), 25 total public hot fixes

FIX: Error 2809 when you execute a stored procedure that takes a table-valued parameter from RPC calls in SQL Server 2014

FIX: Memory is paged out when columnstore index query consumes lots of memory in SQL Server 2014

FIX: A system assert occurs when a Transact-SQL stored procedure with a TVP argument is called from a SQLCLR procedure

FIX: Access Violation when you execute queries on a readable secondary replica of a SQL Server 2014 AlwaysOn Availability Group

FIX: Error 5262 when you execute DBCC CHECKDB on the primary replica that contains repaired data pages in SQL Server 2012 or 2014

FIX: An Always On secondary replica goes into a disconnecting state

FIX: Cannot connect to a named instance after failover on a mirror server in SQL Server 2016 or 2014

FIX: Fails to execute the DBCC CLONEDATABASE command on an in-place upgrade instance of SQL Server

FIX: Incremental statistics runs with higher sample rate than regular statistics when statistics are created or updated in SQL Server 2014

FIX: Incorrect query result when you use varchar(max) variable in the search condition in SQL Server 2014

FIX: Changing the data type and then updating the table with more than 4,000 records causes database corruption

FIX: Memory leak occurs when you run a query that you don’t have enough permissions in SQL Server 2014

FIX: SQL Server is stopped when you install patches on an instance of SQL Server 2014 that contains many databases

 

The reason that I put these lists together is that I want to convince more people to try to keep their SQL Server instances up to date with Cumulative Updates. If you do the proper testing, planning and preparation, I think the risks from installing a SQL Server Cumulative Update are quite low (despite the occasional issues that people run into).

If you install a Cumulative Update or Service Pack on a Production system the day it is released, after doing no testing whatsoever, and then run into problems (and don’t have a plan on how to recover), then I don’t have that much sympathy for you. 

On the other hand, if you go through a thoughtful and thorough testing process, and you have a plan for how you will install the CU, and how you would recover if there were any problems, then you are much less likely to have any problems. You are also much more likely to avoid the issues that are fixed by all of the included fixes in the new build of SQL Server. You have done your job as a good DBA.

Finally, Microsoft has changed their official guidance about whether you should install SQL Server Cumulative Updates. As they say, “we now recommend ongoing, proactive installation of CU’s as they become available”.

SQL Server Diagnostic Information Queries for March 2017

This month, there are minor updates for the SQL Server 2012 and newer version queries.

Rather than having a separate blog post for each version, I have just put the links for all seven major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set. 

Here are links to the latest versions of these queries for SQL Server vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries

SQL Server nNext Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

SQL Server 2005 Blank Results Spreadsheet

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I am not planning on moving these to Github any time soon.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three related Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

SQLskills SQL101: SQL Server Maintenance

Microsoft has a methodology for developing and distributing updates to SQL Server, which they call the Incremental Servicing Model (ISM).  This model has a hierarchy of on-demand hotfixes (HFs), Cumulative Updates (CUs), and Service Packs (SPs) that are used to distribute updates to SQL Server. 

Microsoft’s official policy and guidance about when and whether to apply SQL Server updates changed on March 24, 2016, as described here. It is important that DBAs understand how this update system works whether they are working with traditional on-premises SQL Server or SQL Server running in an Azure VM (or any other IaaS cloud solution such as Amazon EC2).

Why do you need to maintain SQL Server?

Actively maintaining your SQL Server instances by proactively installing CUs and SPs as they become available will make your database server more reliable and possibly perform better. Microsoft has historical CSS data that indicates that a significant percentage of customer issues have already been fixed in a previously released CU, that had not been applied by the customer. My own personal experience as a DBA and consultant reinforces this view.

What happens if I don’t maintain my SQL Server instances?

You are more likely to run into problems that Microsoft has already fixed (because other customers have run into them). If your build of SQL Server is old enough, it may actually become what is called an “unsupported service pack”, which means that Microsoft CSS may be unwilling to fully support you (beyond basic troubleshooting) until you update to a supported service pack level. You don’t want to find yourself in this situation!

Are there any other benefits from updating SQL Server?

Developing a detailed plan for how you test and deploy a SQL Server update, and then actually implementing and updating the plan on a regular basis forces you and your organization to have a plan you also can follow whenever you make any kind of change or update to your database servers or the applications that use them. If you have any sort of HA/DR technology in place, updating SQL Server gives you an opportunity to use it in a planned fashion to minimize your downtime. Doing this on a regular basis validates your HA/DR solution and increases your confidence that it actually works as designed.

Are there any risks from updating SQL Server?

Certainly. Anytime you make any change to a computer system, there is a chance that something can go wrong. That is why you should have a written plan for how you test and deploy a SQL Server update that also includes how to rollback and recover in case something does go wrong. In reality, it is actually quite rare for a SQL Server update to cause a problem, but that doesn’t mean you should not be ready to deal with it if it does happen. Having a detailed plan that you actually follow dramatically decreases the chances of having any issues when you deploy your SQL Server update to Production.

How often does Microsoft release Cumulative Updates?

Microsoft releases Cumulative Updates every eight weeks for the versions of SQL Server that are still in mainstream support. This includes SQL Server 2012, SQL Server 2014, and SQL Server 2016. Currently, the CU release cycles for SQL Server 2012 and SQL Server 2016 are in sync, while SQL Server 2014 releases CUs slightly later. Hopefully, they will get the CU release cycle for all three versions back in sync.

How do I find out about new SQL Server Cumulative Updates?

The first place to look is the SQL Server Release Services blog. You can also check these Microsoft KB articles:

How do I find more information about this subject?

You can watch my Pluralsight courses SQL Server 2012: Installation and Configuration and SQL Server: Installing and Configuring SQL Server 2016, and read my article on SQLPerformance.com, Making the Case for Regular SQL Server Servicing.

You can attend one of our in-person training classes, such as IE0: Immersion Event for the Accidental/Junior DBA or IEHADR: Immersion Event on High Availability and Disaster Recovery. You can also contact me if you have specific questions. And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101

Thanks for reading!

SQL Server 2014 Service Pack 2 Cumulative Update 4

Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 4, which is Build 12.0.5540.0. There are 30 hotfixes in the public fix list. In my opinion, you should be on the SP2 branch by now. If you have not made that move, you should be making plans to get on SP2 as soon as possible.

They have also released SQL Server 2014 Service Pack 1 Cumulative Update 11, which is Build 12.0.4502.0. There are 15 hotfixes in the public fix list for this CU.

There is no corresponding CU for the RTM branch, since SQL Server 2014 RTM is no longer a supported Service Pack level.

New Flagship Xeon E5 and E7 Processors

Intel has recently released two, new “flagship” Xeon processors, one for the E7 v4 product family, and one for the E5 v4 product family. The new Intel Xeon E7-8894 v4 processor has 24 physical cores, and runs at a slightly higher base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E7-8890 v4 processor.

All of the other specifications of the E7-8894 v4 are identical to the earlier E7-8890 v4. One big difference between these two processors is the price. The new Xeon E7-8894 v4 is $8898.00 while the older Xeon E7-8890 v4 is $7174.00, which is a 24% price increase. While this seems like a pretty significant price increase by Intel, I think that most organizations that have a need for this type of hardware are not going to be very sensitive to that difference in hardware cost.

From a SQL Server 2016 Enterprise Edition license cost perspective, each physical core license is $7128.00. A four-socket Dell PowerEdge R930 server would require 96 core licenses, which would cost $684,288.00. The added $6,896.00 hardware cost of four E7-8894 v4 processors vs. four E7-8890 v4 processors is pretty trivial. The base clock speed increase is 9%, which means better single-threaded performance, which actually makes that large investment in SQL Server 2016 licenses more acceptable. Getting 9% more CPU capacity and 9% better single-threaded performance for less than 1% of the total hardware and license cost is actually a pretty good ROI. Table 1 shows some comparative metrics for a four-socket system using either of these two processors.

 

Picture1

Table 1: Comparative Metrics for Xeon E7-8894 v4 vs. Xeon E7-8890 v4 Processors

 

Back in Q4 of 2016, Intel made a similar new flagship model introduction in the Xeon E5 v4 product family with the rollout of the Intel Xeon E5-2699A v4 processor. This new flagship SKU has 22 physical cores and a base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E5-2699 v4 processor. Again, all of the other specifications for the E5-2699A v4 are identical to the earlier E5-2699 v4. There was also a significant price increase for this new flagship processor, with the new SKU costing $4938.00 vs. $4115.00 for the older flagship SKU, which represents a 20% price increase. This also seems like a case of price gouging from Intel, but is is actually acceptable from a SQL Server 2016 license cost perspective.

A two-socket Dell PowerEdge R730 server would require 44 core licenses, which would cost $313,632.00. The added $1,646.00 hardware cost of two E5-2699A v4 processors vs. two E5-2699 v4 processors is even more trivial. Table 2 shows some comparative metrics for a two-socket system using either of these two processors.

 

Picture2

Table 2: Comparative Metrics for Xeon E5-2699A v4 vs. Xeon E5-2699 v4 Processors

 

In both cases, my standard guidance about selecting the fastest available processor for a given physical core count for SQL Server usage still stands. The added hardware cost for getting the fastest processor core is really insignificant compared to the total system cost, including licensing costs.

The fact that Intel feels justified in charging 20-24% more for just 9% more performance is just a sad fact that stems from them not currently having any viable competition in the server CPU space from AMD. I really do hope that the next round of AMD Opteron processors based on the Zen microarchitecture are successful, and start to give Intel some decent competition.

Still, as a SQL Server DBA, getting 9% more capacity and 9% better single-threaded CPU performance for less than 1% higher system cost is actually a pretty good deal.

 

 

SQL Server Diagnostic Information Queries for February 2017

This month, there are minor updates for the SQL Server 2012 and newer queries.

Rather than having a separate blog post for each version, I have just put the links for all seven major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set. 

Here are links to the latest versions of these queries for SQL Server vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries

SQL Server nNext Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

SQL Server 2005 Blank Results Spreadsheet

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I am not planning on moving these to Github any time soon.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three related Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Two Recent Laptops Compared

I have two fairly recent-vintage 13” personal laptops that I use primarily for teaching and presentations that I thought would be interesting to compare from some common performance perspectives. The first one, which is slightly over a year old is a Lenovo Yoga 900, which has a 14nm Intel Core i7-6500U Skylake-U processor, 16GB of RAM, a 512GB Samsung PM871 SATA III SSD, one USB 2.0 port, one USB 3.0 port, one USB-C port and a 3200×1800 touch display.

The newer machine is an HP Spectre x360 13-w023dx, which has a 14nm Intel Core i7-7500U Kaby Lake-U processor, 16GB of RAM, a 512GB Samsung SM961 M.2 NVMe SSD, one USB 3.0 port, two USB-C Thunderbolt 3 ports and a 1080P touch display.

The high-level processor specifications and CPU-Z benchmark results for these two systems are shown below:

 

Processor                     Base Clock      Turbo Clock      Single-threaded CPU          Multi-threaded CPU

Intel Core i7-6500U        2.5GHz            3.1GHz              1467                                    3391

Intel Core i7-7500U        2.7GHz            3.5GHz              1743                                    3958

 

These Skylake-U and Kaby Lake-U processors are quite similar, with the Kaby Lake having an optimized “14nm plus” process technology that lets Intel set the clock speeds slightly higher at the same power usage levels. Kaby Lake also has improved integrated graphics and an improved version of Intel Speed Shift technology that lets Windows 10 throttle up the clock speed of the processor cores even faster than with a Skylake processor.

 

Figure 1: Improved Intel Speed Shift in Kaby Lake

 

The single-threaded CPU-Z 1.78.1 benchmark result is 18.8% higher with the new system, while the multi-threaded CPU-Z benchmark result is 16.7% higher on the new system. I attribute this increase to the higher base and turbo clock speeds, the optimized process technology, and the effect of the improved Intel Speed Shift. The results are shown in figures 2 and 3.

 

image

Figure 2: Intel Core i7-6500U CPU-Z Benchmark Results

 

 

image

Figure 3: Intel Core i7-7500U CPU-Z Benchmark Results

 

Honestly, these current generational CPU performance improvements are slightly better than nothing (but not much), and are certainly not a good enough reason to upgrade from an equivalent Skylake-U system to a Kaby Lake-U system. Where we see a big improvement is with basic storage performance and peripheral connectivity between these two systems.

I was happily surprised that the new HP system came a very fast 512GB Samsung SM961 M.2 NVMe OEM SSD that is equivalent to a Samsung 960 PRO. The reason I was surprised was because some reviews I had read indicated that these HP machines had a much slower Samsung OEM M.2 NVMe SSD. This probably varies by when your machine was manufactured, so perhaps the earliest review machines had the older, slower drives.

As you can see, the difference in the CrystalDiskMark performance between these drives is pretty dramatic.

 

image

Figure 4: 512GB Samsung SM961 M.2 NVMe SSD

 

image

Figure 5: 512GB Samsung PM871 SATA 3 SSD

 

For day to day average PC usage, you probably won’t really notice the difference between a fast SATA 3 SSD and an M.2 PCIe NVMe SSD, but if you are using SQL Server on a laptop, having that extra sequential bandwidth and much better random I/O performance is really noticeable. It is also very nice to have Thunderbolt 3 support, which will allow you to have really fast transfer performance to an appropriate external drive.

So the moral of all this is that the best reason to consider upgrading to a new laptop or new desktop machine for many people are the additional storage and peripheral connectivity options that you can get with a new machine.

 

SQL Server 2012 Cumulative Updates Available

Microsoft has released SQL Server 2012 SP3 CU7 (Build 11.0.6579.0). According to the associated cumulative KB article, KB3205051, 12 issues have been resolved with this update.

Microsoft has also released SQL Server 2012 SP2 CU16 (Build 11.0.5678.0). According to the associated cumulative KB article, KB3205054, 5 issues have been resolved with this update.

If you are running SQL Server 2012, you really should be planning on moving to the SP3 branch (if you haven’t done it already). The SP3 branch will be supported for a longer period of time, and also includes the manageability enhancements that were added in SP3.

Just in case you haven’t noticed, SQL Server 2012 is due to go out of mainstream support on July 11, 2017.

SQL Server 2016 Cumulative Updates Available

Microsoft has released SQL Server 2016 SP1 CU1 (Build 13.0.4411.0). According to the associated cumulative KB article, KB3208177, 63 issues have been resolved with this update. A number of them look to be pretty significant.

Microsoft has also released SQL Server 2016 RTM CU4 (Build 13.0.2193.0). According to the associated cumulative KB article, KB3205052, 65 issues have been resolved with this update.

If you are running SQL Server 2016, you really should be planning on moving to the SP1 branch (if you haven’t done it already) because of all of the manageability enhancements that were added in SP1. Being on SP1 is even more important if you are running SQL Server 2016 Standard Edition, because of all of the feature enhancements that were added in SP1.