Glenn’s Technical Insights For April 29, 2019

(Glenn’s Technical Insights… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can get more visibility. It covers interesting new hardware and software developments that are generally relevant for SQL Server).

SSMS 18.0 Goes GA

SQL Server Management Studio 18.0 became generally available on April 24, 2019. This means that it is the final release version (as opposed to being a preview or release candidate version). It is Build 15.0.18118.0. The Release Notes detail all of the new features, improvements and bug fixes in SSMS 18.0.



Figure 1: SSMS 18.0 About Form


It is officially supported on Windows 10 version 1607 Windows Server 2016, Windows Server 2012 R2 (64-bit), Windows Server 2012 (64-bit), and Windows Server 2008 R2 (64-bit). Older preview versions worked on 64-bit Windows 7 SP1.

If you have a preview or release candidate version of SSMS 18.0 installed, you should uninstall it before you install the GA release version.You can download it here.


SQL Server 2019 CTP 2.5 Available

On April 24, 2019,  Microsoft also released SQL Server 2019 CTP 2.5. Many of the new features in this release are focused on making it easier to deploy and manage “big data clusters”.  This is how Microsoft describes a big data cluster:

Starting with SQL Server 2019 preview, SQL Server big data clusters allow you to deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. These components are running side by side to enable you to read, write, and process big data from Transact-SQL or Spark, allowing you to easily combine and analyze your high-value relational data with high-volume big data.

Kubernetes Cluster

Figure 2: Kubernetes Cluster

There are also improvements in sys.dm_exec_query_plan_stats, including a new database scoped configuration option that lets you control whether last query plan statistics are available at the database level (as opposed to instance-wide with TF 2451).

I think Microsoft must be pushing pretty hard to get SQL Server 2019 to GA status by July 9, 2019, which is when SQL Server 2008 and 2008 R2 fall out of extended support. This is just my own common sense speculation. That’s what I would be pushing for if I were in charge!


AMD Ryzen 7 2700X 5oth Anniversary Edition

AMD is releasing a special 50th Anniversary Ryzen 7 2700X desktop processor. This processor will have the same exact specifications as a normal AMD Ryzen 7 2700X processor, but will come in special gold colored packaging. It will also have a laser engraved signature from AMD President and CEO  Dr. Lisa Su on the heat spreader as shown in Figure 3. This will be covered up when you install the processor.

50th Anniversary Ryzen

Figure 3: 50th Anniversary AMD Ryzen 7 2700X Processor

To be fair, AMD is including a few other things to help justify the cost of this processor compared to a typical Ryzen 7 2700X processor. These include a coupon for a AMD 50th Anniversary T-Shirt, and a special AMD sticker signed by Dr. Su.

AMD Ryzen 7 2700X Package

Figure 4: AMD 50th Anniversary AMD Ryzen 7 2700X Packaging

Some retailers are also adding game bundles to the deal.

Personally, I wouldn’t buy this. It is interesting as a collector’s item, but I am not that much of a collector. It would have been much more interesting if AMD had decided to offer higher specifications on a cherry-picked version of this processor, similar to what Intel did with the special Intel Core i7-8086K processor.




Why You Shouldn’t Use an Intel Xeon Silver Processor for SQL Server


Intel has four main segments in their Second Generation Intel Xeon Scalable Processor Family. These include Xeon Bronze, Xeon Silver, Xeon Gold, and Xeon Platinum. The reason why there are six boxes in Figure 1 is because Intel further segments this with two Xeon Platinum product segments and two Xeon Gold product segments. This is typical over-segmentation by Intel, but it is just the tip of the iceberg as far as their product segmentation goes. That is a topic for another blog post!

2nd Generation Intel Xeon Scalable

Figure 1: Second Generation Intel Xeon Scalable Processor Segments

Modern SQL Server Licensing

Since SQL Server 2012, Microsoft has used core-based licensing rather than socket-based licensing. For non-virtualized servers, you buy core licenses in two-packs, with a minimum of four processor core licenses per physical CPU. These correspond to physical processor cores, not logical processor cores, so Intel HT and AMD SMT is a free benefit. This can give you roughly 25% more overall CPU capacity (certainly not 100% like you might assume by just counting total logical cores).

With virtualized servers, you also buy core licenses in two-packs, with a minimum of four processor core licenses per virtual machine. These correspond to virtual processor cores, which might map to logical cores or physical cores, depending on whether Intel HT or AMD SMT is enabled on the host machine. If you license all of the physical cores on the virtualization host and you have Microsoft Software Assurance, you can forget about counting vCPUs in your VMs, and you can configure them anyway you want to.

A key fact here is that Microsoft does not change the license pricing based on the actual performance of a processor core. It doesn’t matter how old or new the processor is, it doesn’t matter how slow or fast it is, the price per core is exactly the same. You need to be aware of this, and keep it in mind as you select the exact processor to use for SQL Server. Otherwise, you will be losing performance and scalability, or paying for too many SQL Server core licenses, or some combination of both. Each SQL Server 2017 Enterprise Edition core license is $7,128.00.

Intel Xeon Gold Processor Family

The Intel Xeon Gold Processor Family is divided between the entry level two or four-socket Gold 5200 family and the enhanced two or four-socket Gold 6200 family. According to Intel,

“With support for the higher memory speeds, enhanced memory capacity, and four-socket scalability, Intel® Xeon® Gold processors deliver significant improvement in performance, advanced reliability, and hardware-enhanced security. It is optimized for demanding mainstream data center, multi-cloud compute, and network and storage workloads. With up-to four-socket scalability, it is suitable for an expanded range of workloads.”

I think the Intel Gold Processor Family is the sweet spot for most SQL Server workloads. It lets you choose from between 4 and 22 physical cores, with frequency-optimized, lower core count SKUs being available. This lets you get higher single-threaded CPU performance while minimizing your physical core counts, which lets you minimize your SQL Server license costs.

Intel Xeon Silver Processor Family

The Intel Xeon Silver Processor Family is for “enhanced” two-socket servers. According to Intel,

“Intel® Xeon® Silver processors deliver essential performance, improved memory speed, and power efficiency. Hardware-enhanced performance required for entry data center computes, network, and storage.”

Compared to the entry level Intel Xeon Bronze Processor Family, the Silver Family offers hyper-threading, Turbo Boost, DDR4-2400 support, and larger L3 cache sizes. This makes the Bronze family an even worse choice for SQL Server usage.

According to Intel,

“The Intel® Xeon® Bronze processors delivers entry performance for small business and basic storage servers. Hardware-enhanced reliability, availability, and serviceability features designed to meet the needs of these entry solutions.”

Processor Shootout

Let’s compare the Intel Xeon Silver 4208 processor to the Intel Xeon Gold 6244 processor. These are both Second Generation Intel Xeon Scalable processors (Cascade Lake-SP), using the same microarchitecture, so we can directly compare them.

The 14nm Intel Xeon Silver 4208 processor has 8 cores/16 threads with a Base clock speed of 2.10 GHz and a Max Turbo clock speed of 3.20 GHz. It has an 11MB L3 cache and supports DDR4-2400 memory. It has two Ultra Path Interconnect (UPI) links and can scale up to two sockets. It does not support Intel Optane DC Persistent memory. Its thermal design power (TDP) is 85 watts and it has one AVX-512 FMA unit. It has a recommended customer price of $417.00.

The 14nm Intel Xeon Gold 6244 processor has 8 cores/16 threads with a Base clock speed of 3.60GHz and a Max Turbo clock speed of 4.40GHz. It has an 25MB L3 cache and supports DDR4-2933 memory. It has three Ultra Path Interconnect (UPI) links and can scale up to four sockets. It does support Intel Optane DC Persistent memory. Its thermal design power (TDP) is 150 watts and it has two AVX-512 FMA units. It has a recommended customer price of $2,925.00.

You might be thinking “Wow, that is a big difference in the cost of the processors. I could save over $5,000.00 in a two-socket server by choosing the Silver processor instead of the Gold processor”. That is true, but lets take a look at what you would lose by doing that.

If your server has a typical CPU load, most of your processor cores will be running at their base clock speed most of the time. This makes the base clock speed a very important metric for SQL Server usage. There is a huge difference (71.4%) between 2.10 GHz and 3.60 GHz. The base clock speed of the Gold 6244 is actually higher than the Turbo clock speed of the Silver 4208. If your power management is properly configured for best performance, you will always be running a higher clock speed on all cores of the Xeon Gold 6244 than the Xeon Silver 4208 can ever run, even on a single core at full turbo boost speed. The Turbo clock speed difference between the two processors is 37.5%.

The L3 cache of the Gold 6244 is more than twice the size of the Silver 4208. This means there is a much better chance of finding the data you need for a query in the L3 cache rather than in your main DRAM memory. If you do have to go out to main DRAM, your DDR4-2933 memory with the Xeon Gold 6244 will be noticeably faster than the DDR4-2400 memory with the Xeon Silver 4208.

Comparing Estimated TPC-E Scores

So far, there has been one official TPC-E benchmark score on a system with Second Generation Intel Xeon Scalable processors. It is for a two-socket Lenovo ThinkSystem SR650, with two Intel Xeon Platinum 8280 processors. This system had a TPC-E Throughput score of 7,012.53. This is a decent measure of the CPU capacity of this system. We can use this actual score as a baseline for comparison against other Intel processors from the same generation.

Since this system has a total of 56 physical cores, we can divide 7012.53 by 56 to get a score/core of 125.22, which is a good measure of the single-threaded performance of this processor. Remember, this submission is for a system running SQL Server 2017, so it is not just a synthetic benchmark.

For a simple but relatively accurate comparison, we can adjust this score to account for the difference in the number of physical cores and the difference in the base clock speeds between the processor used for the actual TPC-E submission and any other processor from that same generation.

Here is the math for both processors:

Intel Xeon Gold 6244     16 cores/56 cores = .29 core count adjustment      3.60 GHz / 2.70 GHz = 1.33 base clock speed adjustment

7012.53 times 0.29 equals 2003.58, then 2003.58 times 1.33 equals a 2671.44 total estimated score

If we take this total estimated score of 2671.44 divided by 16 total physical cores in the system, we get an estimated 166.97 score/core

Intel Xeon Silver 4208   16 cores/56 cores = .29 core count adjustment      2.10 GHz / 2.70 GHz = 0.78 base clock speed adjustment

7012.53 times 0.29 equals 2003.58, then 2003.58 times 0.78 equals a 1558.34 total estimated score

If we take this total estimated score of 1558.34 divided by 16 total physical cores in the system, we get an estimated 97.40 score/core


These calculation results show that we would have significantly more capacity and much better single-threaded CPU performance with the Intel Xeon Gold 6244 compared to the Intel Xeon Silver 4208. From a SQL Server perspective, the $5000.00 lower hardware cost would be a minor consideration compared to the $114,048.00 license cost for SQL Server 2017 Enterprise Edition.

This methodology does not account for factors such as total L3 cache size, L3 cache size per core, memory speed, and number of UPI links. If these were factored in, the Intel Xeon Gold 6244 would do even better compared to both the Intel Xeon Platinum 8280 and the Intel Xeon Silver 4208.

The difference is so stark that I would consider dropping down to the four-core Intel Xeon Gold 5222 processor, which would save me $56,024.00 in SQL Server 2017 Enterprise Edition license costs on a two-socket system. Unfortunately, Intel has not released a six-core Intel Xeon Gold 6228 processor to replace the old Intel Xeon Gold 6128 processor, so we have a big gap between the Xeon Gold 5222 and the Xeon Gold 6244.

Another alternative would be to have just one Intel Xeon Gold 6244 processor in a two-socket system. If you did that, you would lose half your total memory capacity and half of your total PCIe 3.0 lanes.

A recent conversation on Twitter prompted this post. I would love to hear your thoughts on this either in the comments section or on Twitter. I am GlennAlanBerry on Twitter.

New HP Spectre x360 Laptop


Earlier this week, I bought a new HP Spectre x360 13-AP0023DX convertible laptop at Best Buy. I have often criticized Best Buy as a bad place to buy a computer, but in this case I ignored my own advice for some good reasons. First, this was a great deal for $1050.00. This particular laptop has an Intel Core i7-8565U “Whiskey Lake” processor, 16GB of RAM, a 512GB Toshiba XG5 M.2 NVMe SSD, a 13.3” IPS 4K touchscreen, Intel UHD 620 integrated graphics, two USB-C Thunderbolt 3 ports, and one USB-A 3.0 port. Second, I was planning on swapping out the 512GB Toshiba M.2 NVMe drive for a bigger and faster 1TB Samsung 970 EVO Plus.

The Best Buy computer buying experience has dramatically improved over what it was several years ago. Back then, if you bought a computer, they would doggedly insist that a “Geek Squad” tech needed to unbox your machine, power it on, and “configure” it for you. That service might have been well suited for a non-technical person, but since I am my own Geek Squad, I didn’t need or want it back then.

The main remaining problem with buying a laptop from Best Buy is that their machines will have whatever bloatware the OEM decided to add to their standard Windows 10 Home image. You can try to uninstall everything you don’t want, or you can just install a fresh copy of Windows 10 Professional. If you go with the latter route, you will also need to download and install all of the HP and Intel-specific drivers from the HP Support website. You will probably need to update your main system BIOS and any other firmware that is out of date. You can avoid most of this hassle if you buy a laptop from a Microsoft Store, where they use a very clean, bloatware-free image on their machines. This is called Microsoft Signature Edition.


After getting everything reinstalled and fully updated, I ran a few quick performance tests. This machine is pretty speedy from a CPU and storage perspective. Since it has two PCIe 3.0 x4 Thunderbolt 3 ports, I can use some very fast external storage if I need to. I do wish it had 32GB of RAM.

The purpose of this machine is to be a backup for my main work laptop (a 15” Dell Precision 5520), just in case I ever have problems with it when I am on the road. It only weighs 2.8 pounds, and it came with a touch pen that you can use to draw with as a tablet. It also has a 12-hour battery life, which is very handy. This machine is actually faster than my two-year old Dell Precision 5520 with an Intel Xeon E3-1505M v6 processor.


Figure 1: Intel Core i7-8565U Information

This processor compares pretty well to the old (Q3 2015) Intel Core i7-6700K desktop processor, which is pretty impressive for a mobile processor with only 15W TDP. I have confirmed that it is using Intel Speed Shift in combination with Windows 10. This means that it throttles up it’s clock speed much more quickly.


Figure 2: Intel Core i7-8565U Benchmark Results

It was pretty easy to get to the SSD, after removing six small Philips screws that are hidden under two rubber strips on the bottom of the machine. After swapping out the OEM Toshiba SSD for the 1TB Samsung 970 EVO Plus SSD, I ran CrystalDiskMark, with the results shown below.


Figure 3: CrystalDiskMark 6.0.2 Results

I am very impressed by the 1TB Samsung 970 EVO Plus. I am still waiting for the 2TB model to become available.


Figure 4: 1TB Samsung EVO Plus

Here are a few reviews of this machine:

HP Spectre x360 (13-inch, 2019) Review

HP Spectre x360 13 (2019)

HP Spectre 13 review