SQL Server Diagnostic Information Queries for November 2019

This month, I have just done more minor formatting and documentation improvements, especially for SQL Server 2019.

I have a T-SQL script that you can use to check whether your instance of SQL Server has been patched to mitigate against the Spectre/Meltdown CPU vulnerability. This works for SQL Server 2008 through SQL Server 2017, for on-premises and cloud-based VM (IaaS) usage. You can get the query for this here.

I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the latest version is always a good idea.

Rather than having a separate blog post for each version, I have just put the links for all eleven 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 Managed Instance, Azure SQL Database, SQL Server 2019, SQL Server 2017, SQL Server 2016 SP2, and SQL Server 2016:

SQL Managed Instance Diagnostic Information Queries

SQL Managed Instance Diagnostic Results

Azure SQL Database Diagnostic Information Queries

Azure SQL Database Blank Results Spreadsheet

SQL Server 2019 Diagnostic Information Queries

SQL Server 2019 Blank Results Spreadsheet

SQL Server 2017 Diagnostic Information Queries

SQL Server 2017 Blank Results Spreadsheet

SQL Server 2016 SP2 Diagnostic Information Queries

SQL Server 2016 SP2 Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries

SQL Server 2016 Blank Results Spreadsheet

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

Since SQL Server 2014 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.  SQL Server 2008 R2 and older are also now out of extended support from Microsoft.

I started this policy a while ago, and so far, I have not heard any complaints.

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries

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 six related Pluralsight courses, which are Azure SQL Database: Diagnosing Performance Issues with DMVs, SQL Server 2017: Diagnosing Performance Issues with DMVs, SQL Server 2017: Diagnosing Configuration Issues with DMVs, 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 five of these courses are pretty short and to the point, at 91, 164, 106, 67, 77, and 68 minutes respectively. Listening to these six 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!

Checking To See If Intel Speed Shift Is Enabled

Back in 2015, as part of the Skylake architecture, Intel released a new processor feature called Intel Speed Shift. This is an improved version of Intel SpeedStep, which you may have heard of since it has been around much longer. Essentially, Speed Shift allows the processor and the operating system to cooperate better, and more quickly “throttle up” the clock speed of the processor cores in response to an increased workload.

With the older SpeedStep technology, it would typically take 100-150ms for a processor core to fully ramp up its clock speed in response to a lower P-state. With Speed Shift, this delay goes down to 30-35ms to fully ramp up.


PState_575px

Figure 1: Intel Speed Shift


This technology has been in Intel desktop and mobile processors since Q3 2015 (although you couldn’t use it until Microsoft patched Windows 10 in November 2015). It showed up in Intel server processors in the Skylake-SP family and in the current Cascade Lake-SP family. Figure 2 shows a current Intel Xeon Gold 6244 processor which has Intel Speed Shift support (although you can’t actually tell from CPU-Z).


image

Figure 2: Intel Xeon Gold 6244 in CPU-Z


Remember, you need a new enough processor (Skylake or newer) and operating system support in order to enable Intel Speed Shift. This means a new enough build of Windows 10, Windows Server 2016 or Windows Server 2019.

One way to confirm whether Intel Speed Shift is enabled is to use the free HWiNFO64 utility. On the main HWiNFO screen, in the CPU section, there is a Features section that shows various AMD and Intel processor features. They will be green if that feature is enabled on your system, and greyed out if it is not.

The one for Intel Speed Shift is SST, at the bottom right of the section. You can see this feature enabled in Figure 3.


image

Figure 3: HWiNFO64 Showing Intel Speed Shift Enabled


SST is something you want to have enabled on your database server if at all possible. If you have the two main prerequisites, you may still have to poke around in your BIOS settings to make sure this ends up being enabled. If you don’t see it enabled in HWiNFO64, you might want to bug your server vendor to find out what combination of BIOS settings are required your your model server.




Glenn’s Technical Insights For November 4, 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-oriented items that I find interesting.

AMD and Intel Financial Results for Q3 2019

Normally the financial results of tech companies is not that interesting (unless you work there or own stock in the company). In this case, looking at how AMD and Intel are doing, and comparing their relative size is relevant from an technical perspective since it may help you understand what they are doing with their products and pricing.

AMD has had their best financial quarter since 2005, with 1.8 billion dollars in revenue, and while this sounds impressive, they are still dwarfed by Intel with 19.2 billion dollars in revenue for the quarter. From a net earnings perspective, the picture is even more in Intel’s favor, with Intel posting 6.0 billion in GAAP net income, while AMD posted 120 million in net income for the quarter.

There are a couple of reasons why this matters. First, a resurgent AMD will have more money available for R & D and new product development than they did in the past, which will allow them to maintain their competitive pressure on Intel. On the other hand, Intel has the financial resources and very high margins that will let them lower prices in order to maintain their market share. In the recent past, they haven’t had to do this due to lack of competition from AMD in most market segments.

Since the release of the Zen 2 architecture (with the Ryzen 3000 series desktop processors and EPYC 7002 series server processors), AMD has been reclaiming some market share in those two segments. They have also done well with the Ryzen Threadripper 2000 series HEDT processors, and should do even better with upcoming Zen 2 based Ryzen Threadripper 3000 series HEDT processors. Intel is still doing very well in the mobile segment, which is very important to them.

We have already seen a pretty massive price decrease (over 50%) with the new Intel Cascade Lake-X HEDT processors, as I discussed here. There are pretty strong rumors that Intel is going to announce some price cuts on their mainstream desktop processors pretty soon. I wouldn’t be too surprised to see Intel announce some official price cuts on their Xeon processors in the next few months.

The point here is that AMD has developed into a serious competitor in the mainstream desktop, HEDT, and server market, while Intel is maintaining their dominance in the mobile market. This relative weakness in some segments has already caused Intel to reduce prices on some products, and their financial resources will allow them to do more of that if they want to. Intel has actually touted their financial strength as a key competitive advantage vs. AMD, which makes it even more likely they will announce price cuts on more products. This is good for consumers, but perhaps not so much for Intel stockholders.

AMD AGESA 1.0.0.4 Begins Rolling Out for Ryzen 3000 Series Processors

AGESA stands for AMD Generic Encapsulated Software Architecture. This is a procedure library that AMD developed and maintains, that is supplied to their partner motherboard vendors for use as part of the BIOS of the motherboard. AMD periodically releases new AGESA versions that contain bug fixes and performance enhancements. The motherboard vendors then take this AGESA code and incorporate it into a new BIOS version that you have to download and install on your system.

Even though this may sound trivial, it is actually pretty important if you want to get the best performance and reliability out of your system. Keeping your BIOS up to date is important, whether is is for your laptop, gaming machine, or database server. AGESA 1.0.0.4 improves system boot times by 20-30%, improves turbo clock speed performance and improves NVMe device compatibility, on top of having many other small bug fixes. If you have an AMD desktop system, you should check with your motherboard vendor over the next couple of weeks to get an updated BIOS.

This reminds me of how Tesla pushes out free OTA software updates. They recently pushed out version 2019.36.1, which included a 5% peak power increase for the Model 3, among other new features and improvements. This is the second 5% power increase they have enabled with a software update. Getting extra performance for free is a great thing, whether it is for your car or for your computer.

Intel Delays Release of Cascade Lake-X HEDT Processors

According to WCCFTech (which has a somewhat mixed record regarding rumors and leaks), Intel has decided to slightly delay the planned release of Cascade Lake- X from November 5 to November 25. The supposed reason for this is so Intel can see the pricing for the upcoming AMD Ryzen Threadripper 3000 series HEDT processors, and then decide whether they want to make any pricing adjustments to Cascade Lake-X.

Speaking of that, AMD has scheduled a “Meet The Experts” webinar on November 6, 2019, where they will cover “AMD plans for high-end desktop systems” and “The future of the high-end desktop market”, meaning it is pretty likely they will release more information about the AMD Ryzen Threadripper 3000 series during the webinar.