Glenn’s Technical Insights For July 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).

 

SQL Server Management Studio 18.2 Released

On July 25, 2019, Microsoft released SQL Server Management Studio (SSMS) 18.2. This is mostly a bug fix release, but there are a few new features. These include:

  • Intellisense support for the new OPTIMIZE_FOR_SEQUENTIAL_KEY option for indexes
  • Added a completion time in messages to track when a query completed
  • Added a new attribute in QueryPlan when the inline scalar UDF feature is enabled (ContainsInlineScalarTsqlUdfs)
  • Added SMO support for Feature Restrictions. For more information on the feature itself, see Feature Restrictions

It is becoming more obvious that Microsoft is pushing Azure Data Studio as their preferred tool over SSMS. I don’t think SSMS is going to be abandoned any time soon, but it is clearly getting less love than Azure Data Studio.

 

image

Figure 1: SSMS 18.2

The complete release notes are here. You can download SSMS 18.2 here.

 

SQL Server 2014 Cumulative Updates

Even though SQL Server 2014 fell out of mainstream support on July 9, 2019, there is actually one final set of Cumulative Updates for SQL Server 2014 that were released on July 29, 2019. These include:

SQL Server 2014 SP3 CU4 (Build 12.0.6329.1)

SQL Server 2014 SP2 CU18 (Build 12.0.5687.1)

There are four public hotfixes in SQL Server 2014 SP2 CU18, and there are nineteen public hotfixes in SQL Server 2019 SP3 CU4. If you are on SQL Server 2014, you should get on SQL Server 2014 SP3 CU4 eventually, since that is going to be the last released build (unless there are any security updates between now and July 9, 2024, which is pretty likely).

There are also probably going to be the latest round of Cumulative Updates for SQL Server 2016 and SQL Server 2017 later this week, so keep your eye out for that.

 

 

 

Building an AMD Ryzen 7 3700X Desktop Machine

Since the AMD Ryzen 3000 series processors were released on July 7, 2019, I decided to build a new desktop machine based on this processor. This was to give me some hands on experience with an AMD Ryzen 3000 series processor, and because I actually enjoy building desktop systems. This new machine will be used primarily to play “World of Tanks”, and I wanted to see if I could build a fairly low-end machine that would perform significantly better than my existing gaming rig.

My old gaming system has a $350.00 6C/12T Intel Core i7-8700K and an older $650.00 AMD R9 Fury Nano video card.

I live fairly close to a Micro Center, so I can take advantage of their bundle discounts when you buy the right set of components together. This can save you over $100 on a complete system.

Component Selection

I’m going to walk through what exact components I selected for the system, with a little bit of reasoning why I made those choices. In every case, I could have spent less money without giving up too much performance.


Processor

I decided to get an AMD Ryzen 7 3700X, which is an 8C/16T processor that is a mid-range processor in the Ryzen 3000 series. It has a base clock of 3.6GHz and a max boost clock of 4.4GHz, with a 32MB L3 cache. This particular model has a 65 watt TDP (so it uses less power than the AMD Ryzen 7 3800X), and it comes with a pretty decent RGB Wraith Prism cooler. The Micro Center price was $329.99.

amd-ryzen-7-3700x-sweet-spot

Figure 1: The Sweet Spot Processor


Motherboard

I selected an ASRock X570 Phantom Gaming 4 ATX motherboard.  I tend to favor ASRock motherboards, and all of my recent builds have ASRock motherboards. This motherboard uses the high-end X570 chipset, but it is one of the more affordable models from ASRock.

If you have an existing 300 or 400 series AMD chipset motherboard, you can probably use that with a new Ryzen 3000 series processor, with a BIOS update. You will lose out on PCIe 4.0 support, but that is not a big deal for a gaming system. The Micro Center price was $154.99, plus a $50.00 bundle discount, since I was buying an eligible processor.

Your-PC-Transformed-X570%20Phantom%20Gaming%204

Figure 2: ASRock X570 Phantom Gaming 4


Memory

I selected a 16GB G.Skill Trident Z CL15 F4-3600C15D-16GTZ kit (Micro Center SKU 822635), which is two 8GB sticks of memory. Having two sticks of memory puts you in dual-channel mode. Having two sticks of memory instead of four sticks lets you use one DIMM per channel (DPC) which increases your memory performance. TweakTown reviewed this memory here.

Memory performance is actually quite important for AMD Ryzen 3000 series processors. Having higher speed memory with tighter timings makes a significant difference on many synthetic and real-world benchmarks. The price/performance sweet spot seems to be DDR4-3200, with DDR4-3600 being slightly better. This G.Skill Trident Z kit has tight CL15 timing, which is important. The Micro Center price was $199.99, but I actually found an open box kit for $183.96


Video Card

I selected an XFX Radeon 5700 8GB video card. This is the low-end SKU in the Radeon 5700 series. The Radeon 5700 uses less power than the Radeon 5700 XT, but the performance is fairly close in most benchmarks. I do my gaming at 2K (2560 x 1440), so video performance is my main bottleneck. Truth be told, I would have probably picked a 5700 XT card, but Micro Center didn’t have any in stock on July 8, 2019.

The Micro Center price was $349.99, plus a $50.00 bundle discount, since I was buying an eligible processor.


Storage

I bought a 500GB Samsung 970 EVO NVMe M.2 card. This was actually not what I meant to buy (which was a newer and slightly faster 500GB Samsung 970 EVO Plus), but it was my fault. I had written down my component list and handed it to a sales person to get the components that are locked up, and I simply wrote “500GB Samsung 970 EVO”, so I got exactly what I asked for.  I didn’t notice it until I got home.

A 500GB Samsung 860 EVO SATA SSD is only about $10.00 less than the much faster 500GB 970 EVO. If you use a SATA SSD, you will have to use a SATA data cable and a SATA power cable, which makes cable management more difficult. An M.2 drive goes directly on the motherboard, with no cables required.

As it turns out, storage performance is not typically a bottleneck on a gaming system, as long as you are not using a slow magnetic hard drive. The Micro Center price was $89.99.

001_gallery_MZ-V7E500BW_09-28-18

Figure 3: Samsung 970 EVO


Power Supply

I selected a Corsair RM750x 750 Watt 80 Plus Gold ATX Modular Power Supply. Modular power supplies make cable management much easier, since you only have to install the cables you are actually using. Because of my other component selections, I only needed cables for the motherboard/CPU and for the video card. No SATA or Molex cables were required.

This is a very good quality power supply that is less money than the Seasonic power supplies that I usually buy. Unfortunately, Micro Center doesn’t carry the full Seasonic line of power supplies. Tom’s Hardware reviewed this unit here.

The Micro Center price was $119.99, plus a $10.00 bundle discount, since I was buying an eligible processor.


Case

I selected a Fractal Design Meshify C Tempered Glass MidTower ATX case. This case has very good airflow, and it is still pretty quiet. It is also quite low on the RGB bling factor, which is fine with me. It is a relatively small ATX case, which makes the build a little more difficult than a larger case. It does have good cable management features though. Gamers Nexus reviewed the Meshify C here.

One thing I might do is to replace the stock 120mm fans with 140mm Corsair ML fans, which would move more air and be more quiet. The Micro Center price was $99.99.


Build Notes

You want to make sure you are running Windows 10 Version 1903, and that you get the latest AMD Chipset drivers so that you will get the Windows scheduler fix for Zen processors and the much faster clock speed ramp-up times for Zen 2 processors. You also want to make sure you get the latest main BIOS version for your motherboard and then make an effort to keep it up to date. This will give you the latest AMD AGESA code, which helps memory performance, among other things.

If you get a Samsung NVMe M.2 drive, make sure to install the Samsung NVMe driver and to install Samsung Magician, so you can stay current with the drive firmware. At a bare minimum, you will probably want to enable XMP in your main BIOS. There are also a number of AMD-specific BIOS settings for things like Precision Boost Overdrive to experiment with.


Conclusion

The total (before tax) for the complete system was $1,218.90. This new system is significantly faster than my old gaming system, and it uses less power, while costing less money. I could have saved more money with some different component choices. For example, I could have gotten a cheaper B450 or X470 motherboard and an AMD Ryzen 5 3600 processor. I could have used less expensive memory, and gotten a less expensive power supply and case. By doing all of that, I could probably get the price down to around $900.00 for a 6C/12T system that would have pretty comparable gaming performance.

I’ve have some benchmark results in an upcoming post.




Diagnostic Information Queries for SQL Managed Instance

I’ve put together an initial set of Diagnostic Information Queries for SQL Managed Instance. These were originally based on my SQL Server 2019 Diagnostic Information Queries, with any non-functional or non-relevant queries removed. I’ve removed most of the non-relevant columns from the existing queries, and I have removed most of the non-relevant comments and links.

I am still working on adding new managed instance-specific queries to the set.

key-features

You can get these queries and the matching results spreadsheet from the links below:

SQL Managed Instance Diagnostic Information Queries

SQL Managed Instance Diagnostic Results

 

I’ll be adding these to my monthly update blog post where I have all of the other diagnostic information queries. As always, please send me any feedback that you have about these. Thanks!