Glenn’s Tech Insights For September 26, 2019

(Glenn’s Tech 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). It also can just be technically related items that I find interesting.

Useful Utility/Benchmark Software Updates

Samsung has released a new version of Samsung Magician, which is now version 6.0. This release has a completely new user interface, and quite a bit of additional functionality. I have noticed that it takes longer to startup and scan your drives compared to the previous version. If you have any Samsung SSDs in your system, you really should have their Magician software, especially for obtaining and installing firmware updates.

Samsung Magician

Figure 1: Samsung Magician 6.0

CrystalDiskMark 7 Beta 4 is available, with many new features along with additional testing and display modes. These include displaying IOPS and latency information in the main GUI.

You can also choose from different testing profiles, which essentially let you do similar testing compared to running Microsoft DiskSpd natively. This is going to make CDM a much more useful tool.


Figure 2: CrystalDiskMark 7.0 Beta4 – Default Profile


Figure 3: CrystalDiskMark 7 Text Results

This is one of the new testing profiles.


Figure 4: CrystalDiskMark 7.0 Beta4 – Peak Performance + Mix Profile

2nd Generation Intel Optane DC SSDs Coming in 2020

I’ve been very impressed with the 1st generation Intel Optane SSD DC P4800X “Coldstream” storage devices that use two-layer 3D XPoint media. They have been available since mid-2017, and they are extremely well-suited for certain types of SQL Server I/O workloads. One prime example is write heavy tempdb workloads.

Now, Intel is starting to reveal more details about the 2nd generation devices in this family. These are code named “Alder Stream”, and they will use Barlow Pass 3D XPoint media with four layers. It is also probable that they will have PCIe 4.0 support, and a new, faster controller. They are due for release in 2020, and they will have up to double the capacity and significantly better performance than the current generation.

One key advantage of these devices is that they will work with legacy versions of SQL Server and legacy hardware. As long as you have PCIe 3.0 x4 hardware support and your OS supports NVMe drivers, you can use them.

Alder Stream Slide

Figure 5: Intel Memory and Storage Roadmap

Some Comparative CPU-Z Benchmark Scores

About a month ago, I built a new desktop gaming system based on an AMD Ryzen R7-3700X 8C/16T processor. I mainly use that system to play World of Tanks at 2K (2560 x 1440), and this new system has significantly higher frame rates at 2K (typically 95-120 fps) than my previous system. When you are gaming at 2K or 4K, your video card is going to be your main bottleneck, unless your CPU is extremely slow. I am using the stock AMD RGB Wraith Prism CPU cooler, and the only tweak I have done so far is to enable the XMP memory profile in the BIOS so that my G.Skill Trident Z CL15 DDR4-3600 memory is running at full speed.

I previously promised some benchmarks on the new system, so one very quick and easy one is the CPU-Z benchmark. This test only takes about 15 seconds, and it is part of the very useful  CPU-Z utility, which requires no installation. This makes it very easy to run on a system, whether it is a gaming rig or a VM that will be running SQL Server. Figure 1 shows an example result on my AMD Ryzen Threadripper 2950X workstation.


Figure 1: Example CPU-Z Benchmark Results

Today, I decided to run the CPU-Z 1.89.1 CPU benchmark on eight different systems that I have around the house. Two of these are high-end desktops (HEDT), three are mainstream desktops, and three are laptops. The ST Score is the single-threaded score, while the MT score is the multi-threaded score.

The oldest system in the bunch is the 14nm Intel Core i7-6700K from Q3 2015, while the newest is the 7nm AMD Ryzen R7-3700X from Q3 2019. My AMD Ryzen Threadripper systems don’t quite have the same single-threaded CPU performance as the mainstream desktop systems, but they do have a lot more cores (and PCIe 3.0 lanes).

CPU-Z scores

Figure 2: Comparative CPU-Z Benchmark Scores

None of these systems are the current “top of the line” anymore. The AMD Ryzen R7-3700X is roughly in the middle of the stack for the AMD Ryzen 3000 series. The value proposition of the AMD Ryzen 3000 series is that you get great multi-threaded CPU performance, and close enough single-threaded CPU performance for significantly less money than comparable Intel mainstream desktop processors. You also get PCIe 4.0 support with an X570 motherboard.

Rumor has it that AMD may introduce the 3rd generation Ryzen Threadripper processors as soon as September 7, 2019, supposedly with new X599 motherboards to enable PCIe 4.0 support.

Glenn’s Tech Insights For June 30, 2019

(Glenn’s Tech 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).

Leaked Intel Internal Memo About AMD

There was quite a bit of discussion and commentary last week about a leaked memo from an internal Intel “Circuit News” employee portal. This memo, which is entitled AMD competitive profile: Where we go toe-to-toe, why they are resurgent, which chips of ours beat theirs“. The memo is a pretty frank analysis of Intel’s current challenges in the desktop and server CPU market. Since it was written for internal consumption at Intel, it also seems to be little bit of a “buck up the troops” document, which seems understandable in that context.

The 7nm AMD Ryzen 3000 series processors and new X570 chipset motherboards are going to be on store shelves on July 7, 2019. The review embargos will expire on the same day, so we will probably have many reviews and benchmark numbers from a multitude of reputable 3rd party review sites. That is when we will finally know for sure whether the AMD Ryzen 3000 series processors actually have better single-threaded CPU performance than the best modern Intel processors.

This matters for SQL Server because the upcoming (Q3 2019) AMD 7nm EPYC “Rome” processors use the same Zen 2 architecture as the Ryzen 3000 desktop processors. If Ryzen 3000 lives up to the expectations and hype, that will be a good omen for “Rome”. SQL Server core-based licensing makes single-threaded CPU performance important if you want to maximize CPU performance and capacity while keeping license costs under control. Having a viable alternative to Intel for server processors is good for the market. Strong competition between AMD and Intel will force both companies to continue to innovate at a more rapid pace.

Despite what you may read and hear, Intel is not going to completely fail in those markets. They will lose some market share, and they will probably have to respond by offering lower prices for many of their existing processors. Intel is definitely under pressure in those two market segments, and they just don’t have an announced product release that will be a good competitive response for at least six to twelve months.

Microsoft Releases SQL Server 2019 CTP 3.1

On June 26, 2019, Microsoft released SQL Server 2019 CTP 3.1. The release notes are here. Some of the highlights for the database engine include the ability to have the SQL Server setup program suggest recommended MIN and MAX Server memory values (which you can override) during installation.

SQL Server 2019 CTP 3.1

Figure 1: Memory Configuration During Installation

Another improvement is a new option for indexes, which is OPTIMIZE_FOR_SEQUENTIAL_KEY. This lets you enable an optimization within the database engine that helps improve throughput for high-concurrency inserts into the index. This option is intended for indexes that are prone to last-page insert contention, typically seen with indexes that have a sequential key such as an identity column, sequence, or date/time column.

Microsoft continues to add new features to every SQL Server 2019 CTP release. So far, SQL Server 2019 looks like it is going to be good version release with a lot of genuinely useful new features and improvements compared to SQL Server 2017.