SQLskills at 24 Hours of PASS: Summit Preview 2019

On September 10, 2019, SQLskills will have three slots in the free 24 Hours of PASS: Summit Preview 2019 event. I’ll be leading off with “Dr. DMV’s Troubleshooting Toolkit” at 12:00 UTC. Erin Stellato will present “Why You Need Query Store” at 16:00 UTC, and Jonathan Kehayias will present “Bigger Hardware or Better Code and Design?” at 19:00 UTC. You can use this link to register for any or all of the sessions in the entire event.

Here are the abstracts for the three sessions:

Dr. DMV’s Troubleshooting Toolkit

Dynamic Management views and functions allow you to easily see exactly what is happening inside your SQL Server instances and databases with a high level of detail. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session presents, demonstrates, and explains a complete set of diagnostic DMV queries that you can easily use to detect and diagnose configuration and performance issues in your SQL Server instances and databases. This session goes into exhaustive detail on how to interpret the results of each of the diagnostic queries, including relevant background information on how to properly configure your hardware, storage subsystem, operating system, SQL Server instance, and databases in order to avoid performance and scalability issues.

Why You Need Query Store

Have you upgraded to SQL Server 2016 or higher, but still have databases using the old Cardinality Estimator? Do you know that you have queries with inconsistent performance, but you’re just not sure how to find them, or fix them, quickly? Are you tired of flailing around in SQL Server, querying DMV after DMV to figure out the *real* problem with performance? Query Store can help. We’ll cover Query Store end-to-end in this full day workshop built using real-world examples based on customer issues resolved over the last 2+ years. You’ll understand how to configure it, what data it captures, and how to use it to analyze performance, find regressions, and force plans. The demos will teach you how to find common patterns in query performance using T-SQL, and how to understand your workload. This class is applicable for those running SQL Server 2016 or higher (or planning to upgrade), or Azure SQL Database, and will provide practical and applicable information you can use whether you’re a new or veteran DBA, a developer that has to troubleshoot query performance, or an application administrator just trying to keep the system afloat. You’ll learn how to find and leverage important information in Query Store to make solving common performance problems easier the moment you walk back into the office.

Bigger Hardware or Better Code and Design?

Whether you are running SQL Server in the cloud or on-premise, more hardware often becomes the first answer to performance problems. The cost of scaling up in effort is relatively low, especially in the cloud where changing resource sizes is only a drop down option away, but financially this can quickly become a deal breaker. Even the fastest hardware won’t keep up with bad design and coding patterns. This session will take a look at many of the new features of SQL Server and how they can best be leveraged for performance tuning your workload. From In-Memory OLTP to Columnstore Indexes, Query Store, and Extended Events, this session will guide you in finding the source of the problems, and solutions available to make your workload faster and consistently reliable. We’ll even take a look at different alternatives using older features of SQL Server that you may be leaving on the shelf that are a better fit to solving certain kinds of problems.

Erin Stellato recently wrote about the SQLskills presence at PASS Summit 2019, where both Erin and Jon have full day pre-conference workshops, and I have a half-day session during the conference. I hope to see you both at 24 Hours of PASS and at the PASS Summit 2019!

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

It also can just be technically related items that I find interesting.

SQL Server 2019 Release Candidate

On August 21, 2019, Microsoft announced the availability of the first public release candidate of SQL Server 2019. I find it interesting that Microsoft just called this “SQL Server 2019 Release Candidate” rather than Release Candidate 1 like they have done with previous versions of SQL Server. That sort of implies that there won’t be any more public release candidates before GA.

The looming question is when SQL Server 2019 will actually go GA. The conventional wisdom is that it will happen during the first week of November, while Microsoft Ignite (and PASS Summit 2019) are happening. It will probably be Wednesday, November 6, 2019, during the Day 1 Keynote Session at the PASS Summit. That is my guess.

Regardless of that, I think SQL Server 2019 is going to be a worthwhile release that should help convince more organizations to finally upgrade from their legacy versions of SQL Server.

What’s New in SQL Server 2019

SQL Server 2019 Preview Release Notes

 

See the source image

Figure 1: Top Ten Reasons to Choose SQL Server 2019

 

August 2019 Release of Azure Data Studio

On August 15, 2019, Microsoft released the August 2019 version of Azure Data Studio, which is version 1.10. Microsoft continues to maintain a monthly release cycle for Azure Data Studio. In my mind, this probably means that you should start using Azure Data Studio at least enough so that you are familiar with it.

I think it is going to be a tough “ask” for Microsoft to get veteran DBAs to completely switch to Azure Data Studio rather than using SSMS for most of their daily work. They would probably deny that is even their intent. At the same time, if SSMS is only updated every several months, and if Azure Data Studio continues to get new features to the point where it is seen as more useful than SSMS, then it may eventually become inevitable.

Don’t get me wrong, I am not hostile to Azure Data Studio. In fact, I plan on using it to present Dr. DMV’s Troubleshooting Toolkit at the PASS Summit 2019. I plan on building a SQL Notebook for that.

 

image

Figure 2: Azure Data Studio

 

 

 

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.


image

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.