SQL Server 2014 Service Pack 2 Cumulative Update 4

Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 4, which is Build 12.0.5540.0. There are 30 hotfixes in the public fix list. In my opinion, you should be on the SP2 branch by now. If you have not made that move, you should be making plans to get on SP2 as soon as possible.

They have also released SQL Server 2014 Service Pack 1 Cumulative Update 11, which is Build 12.0.4502.0. There are 15 hotfixes in the public fix list for this CU.

There is no corresponding CU for the RTM branch, since SQL Server 2014 RTM is no longer a supported Service Pack level.

New Flagship Xeon E5 and E7 Processors

Intel has recently released two, new “flagship” Xeon processors, one for the E7 v4 product family, and one for the E5 v4 product family. The new Intel Xeon E7-8894 v4 processor has 24 physical cores, and runs at a slightly higher base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E7-8890 v4 processor.

All of the other specifications of the E7-8894 v4 are identical to the earlier E7-8890 v4. One big difference between these two processors is the price. The new Xeon E7-8894 v4 is $8898.00 while the older Xeon E7-8890 v4 is $7174.00, which is a 24% price increase. While this seems like a pretty significant price increase by Intel, I think that most organizations that have a need for this type of hardware are not going to be very sensitive to that difference in hardware cost.

From a SQL Server 2016 Enterprise Edition license cost perspective, each physical core license is $7128.00. A four-socket Dell PowerEdge R930 server would require 96 core licenses, which would cost $684,288.00. The added $6,896.00 hardware cost of four E7-8894 v4 processors vs. four E7-8890 v4 processors is pretty trivial. The base clock speed increase is 9%, which means better single-threaded performance, which actually makes that large investment in SQL Server 2016 licenses more acceptable. Getting 9% more CPU capacity and 9% better single-threaded performance for less than 1% of the total hardware and license cost is actually a pretty good ROI. Table 1 shows some comparative metrics for a four-socket system using either of these two processors.

 

Picture1

Table 1: Comparative Metrics for Xeon E7-8894 v4 vs. Xeon E7-8890 v4 Processors

 

Back in Q4 of 2016, Intel made a similar new flagship model introduction in the Xeon E5 v4 product family with the rollout of the Intel Xeon E5-2699A v4 processor. This new flagship SKU has 22 physical cores and a base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E5-2699 v4 processor. Again, all of the other specifications for the E5-2699A v4 are identical to the earlier E5-2699 v4. There was also a significant price increase for this new flagship processor, with the new SKU costing $4938.00 vs. $4115.00 for the older flagship SKU, which represents a 20% price increase. This also seems like a case of price gouging from Intel, but is is actually acceptable from a SQL Server 2016 license cost perspective.

A two-socket Dell PowerEdge R730 server would require 44 core licenses, which would cost $313,632.00. The added $1,646.00 hardware cost of two E5-2699A v4 processors vs. two E5-2699 v4 processors is even more trivial. Table 2 shows some comparative metrics for a two-socket system using either of these two processors.

 

Picture2

Table 2: Comparative Metrics for Xeon E5-2699A v4 vs. Xeon E5-2699 v4 Processors

 

In both cases, my standard guidance about selecting the fastest available processor for a given physical core count for SQL Server usage still stands. The added hardware cost for getting the fastest processor core is really insignificant compared to the total system cost, including licensing costs.

The fact that Intel feels justified in charging 20-24% more for just 9% more performance is just a sad fact that stems from them not currently having any viable competition in the server CPU space from AMD. I really do hope that the next round of AMD Opteron processors based on the Zen microarchitecture are successful, and start to give Intel some decent competition.

Still, as a SQL Server DBA, getting 9% more capacity and 9% better single-threaded CPU performance for less than 1% higher system cost is actually a pretty good deal.

 

 

SQL Server Diagnostic Information Queries for February 2017

This month, there are minor updates for the SQL Server 2012 and newer queries.

Rather than having a separate blog post for each version, I have just put the links for all seven 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 Server vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries

SQL Server nNext Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

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

Since SQL Server 2008 R2 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.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

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 three related Pluralsight courses, which are 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 three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three 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!

Two Recent Laptops Compared

I have two fairly recent-vintage 13” personal laptops that I use primarily for teaching and presentations that I thought would be interesting to compare from some common performance perspectives. The first one, which is slightly over a year old is a Lenovo Yoga 900, which has a 14nm Intel Core i7-6500U Skylake-U processor, 16GB of RAM, a 512GB Samsung PM871 SATA III SSD, one USB 2.0 port, one USB 3.0 port, one USB-C port and a 3200×1800 touch display.

The newer machine is an HP Spectre x360 13-w023dx, which has a 14nm Intel Core i7-7500U Kaby Lake-U processor, 16GB of RAM, a 512GB Samsung SM961 M.2 NVMe SSD, one USB 3.0 port, two USB-C Thunderbolt 3 ports and a 1080P touch display.

The high-level processor specifications and CPU-Z benchmark results for these two systems are shown below:

 

Processor                     Base Clock      Turbo Clock      Single-threaded CPU          Multi-threaded CPU

Intel Core i7-6500U        2.5GHz            3.1GHz              1467                                    3391

Intel Core i7-7500U        2.7GHz            3.5GHz              1743                                    3958

 

These Skylake-U and Kaby Lake-U processors are quite similar, with the Kaby Lake having an optimized “14nm plus” process technology that lets Intel set the clock speeds slightly higher at the same power usage levels. Kaby Lake also has improved integrated graphics and an improved version of Intel Speed Shift technology that lets Windows 10 throttle up the clock speed of the processor cores even faster than with a Skylake processor.

 

Figure 1: Improved Intel Speed Shift in Kaby Lake

 

The single-threaded CPU-Z 1.78.1 benchmark result is 18.8% higher with the new system, while the multi-threaded CPU-Z benchmark result is 16.7% higher on the new system. I attribute this increase to the higher base and turbo clock speeds, the optimized process technology, and the effect of the improved Intel Speed Shift. The results are shown in figures 2 and 3.

 

image

Figure 2: Intel Core i7-6500U CPU-Z Benchmark Results

 

 

image

Figure 3: Intel Core i7-7500U CPU-Z Benchmark Results

 

Honestly, these current generational CPU performance improvements are slightly better than nothing (but not much), and are certainly not a good enough reason to upgrade from an equivalent Skylake-U system to a Kaby Lake-U system. Where we see a big improvement is with basic storage performance and peripheral connectivity between these two systems.

I was happily surprised that the new HP system came a very fast 512GB Samsung SM961 M.2 NVMe OEM SSD that is equivalent to a Samsung 960 PRO. The reason I was surprised was because some reviews I had read indicated that these HP machines had a much slower Samsung OEM M.2 NVMe SSD. This probably varies by when your machine was manufactured, so perhaps the earliest review machines had the older, slower drives.

As you can see, the difference in the CrystalDiskMark performance between these drives is pretty dramatic.

 

image

Figure 4: 512GB Samsung SM961 M.2 NVMe SSD

 

image

Figure 5: 512GB Samsung PM871 SATA 3 SSD

 

For day to day average PC usage, you probably won’t really notice the difference between a fast SATA 3 SSD and an M.2 PCIe NVMe SSD, but if you are using SQL Server on a laptop, having that extra sequential bandwidth and much better random I/O performance is really noticeable. It is also very nice to have Thunderbolt 3 support, which will allow you to have really fast transfer performance to an appropriate external drive.

So the moral of all this is that the best reason to consider upgrading to a new laptop or new desktop machine for many people are the additional storage and peripheral connectivity options that you can get with a new machine.

 

SQL Server 2012 Cumulative Updates Available

Microsoft has released SQL Server 2012 SP3 CU7 (Build 11.0.6579.0). According to the associated cumulative KB article, KB3205051, 12 issues have been resolved with this update.

Microsoft has also released SQL Server 2012 SP2 CU16 (Build 11.0.5678.0). According to the associated cumulative KB article, KB3205054, 5 issues have been resolved with this update.

If you are running SQL Server 2012, you really should be planning on moving to the SP3 branch (if you haven’t done it already). The SP3 branch will be supported for a longer period of time, and also includes the manageability enhancements that were added in SP3.

Just in case you haven’t noticed, SQL Server 2012 is due to go out of mainstream support on July 11, 2017.

SQL Server 2016 Cumulative Updates Available

Microsoft has released SQL Server 2016 SP1 CU1 (Build 13.0.4411.0). According to the associated cumulative KB article, KB3208177, 63 issues have been resolved with this update. A number of them look to be pretty significant.

Microsoft has also released SQL Server 2016 RTM CU4 (Build 13.0.2193.0). According to the associated cumulative KB article, KB3205052, 65 issues have been resolved with this update.

If you are running SQL Server 2016, you really should be planning on moving to the SP1 branch (if you haven’t done it already) because of all of the manageability enhancements that were added in SP1. Being on SP1 is even more important if you are running SQL Server 2016 Standard Edition, because of all of the feature enhancements that were added in SP1.

Hardware Selection for a Home Lab – Part 1

I have long believed that if you want to be more successful as a developer, database professional, or in any technically-oriented I.T. career, it is a very good idea to have some sort of home lab environment to use for experimentation and learning.

Depending on your goals, needs, budget, available space, etc., there are many different ways to actually go about doing this. Some of these include using actual rack-mount or tower servers (which might be old, used equipment or newer equipment from a server vendor’s outlet), using desktop workstation tower servers built from parts (or purchased used), and even using old laptop machines.

Again, based on your needs, preferences, and available overall infrastructure, it may make more sense to use virtualization, with one or more fairly robust virtualization host machines that can run multiple, concurrent virtual machines (VMs) or to just have a number of “bare metal” machines.

One factor that is often ignored as people think about this is the spouse acceptance factor (SAF), where what you might like to do must be tempered by what your spouse will actually accept in terms of noise, space, heat and power usage. Having a number of rack mount servers running 24 x 7 can have a very negative impact on your electrical bill!

You also need to think about the network and storage infrastructure required to properly support your home lab. At a bare minimum, you will probably want wired Gigabit Ethernet, but you may want to think about having some 10 Gigabit Ethernet capability, especially as 10Gb equipment prices continue to decline. You also might want a decent NAS device to support your lab.

Using a NUC

One increasingly viable method for equipping a home lab is to use one or more Small Form Factor (SFF) machines, such as the Intel NUC series. These are very small footprint, bare-bone kits that include everything you need except RAM, storage, and an operating system. You buy the NUC kit, then buy and install your RAM and storage, install the operating system of your choice (with some important limitations), and then you are ready to go.

These machines are typically based on low power, dual-core laptop processors (with hyper-threading), using laptop SO-DIMM RAM, and have had somewhat limited storage capability, at least for earlier generations. These type of machines have been around for about four years now, with several generations that have been released from Intel and other smaller companies, such as Gigabyte. Each new generation has had various improvements that make a SFF machine a more realistic choice for many lab scenarios.

Intel has recently announced a new generation of NUCs that are based on the 7th generation Kaby Lake processor. The main potential advantage of this new generation is increased storage space and performance. The Core i7 and Core i5 models have 40Gbps Thunderbolt 3 support built-in with an Alpine Ridge controller that connects to a USB-C connector. You can get a primer about the various USB and Thunderbolt connectors and standards here.

Having Alpine Ridge built-in gives you a lot more I/O flexibility and performance. For example, you could plug in an external enclosure that uses USB 3.1 Gen 2 (up to 10 Gbps) or Thunderbolt 3 (up to 40 Gbps) connectivity to get a lot of additional I/O throughput from your NUC.

The two best choices from this generation (for a home lab) are the Intel NUC7i7BNH and the Intel NUC7i5BNH, which are the taller Core i7 and Core i5 versions respectively. The reason why you want the taller models is because they let you install a 2.5” SATA III SSD in addition to an M.2 2280 PCIe 3.0 NVMe storage card, which gives you additional storage space and performance.

The Intel NUC7i7BNH uses the Intel Core i7-7567U processor, which has a base clock speed of 3.5GHz, a Turbo clock speed of 4.0GHz, a TDP of 28W, and a 4MB L3 cache. The Intel NUC7i5BNH uses the Intel Core i5-7260U processor, which has a base clock speed of 2.2 GHz, a Turbo clock speed of 3.4GHz, a TDP of 15W, and a 4MB L3 cache. Both of these processors have integrated Iris Plus graphics with 64GB of eDRAM, which shows up as L4 cache. Whether or not Windows and SQL Server will take advantage of that L4 cache for general computing purposes is unknown to me right now.

A fully loaded Intel NUC7i7BNH would have four logical processor cores, 32GB of 2133MHz DDR4 RAM, wired Gigabit Ethernet, wireless 802.11ac networking, up to a 4TB SATA III SSD, up to a 2TB M.2 PCIe NVMe, plus whatever storage you plugged into the Thunderbolt 3 USB-C port. A system configured like this would be pretty expensive, mainly because of the storage costs for the top of the line components. The ultimate performance bottleneck for most scenarios would be the number of available processor cores, even though these will be pretty fast cores.

Another potential issue is that you might not be able to use a Server operating system such as Windows Server 2016 or Windows Server 2012 R2, not because the OS won’t install, but because of driver issues with the NIC. Quite often, Intel won’t let you install server-class NIC drivers on client-grade NIC hardware. Sometimes people figure out ways to hack around this, but often it is much easier to install Windows 10 Professional, and then use Windows Hyper-V to host VMs.

 

Figure 1: Rear Panel of Intel NUC7i7BNH

 

On the software side of your home lab, there are many free resources available from Microsoft. If you are a student or faculty member at a high school or university, you can take advantage of Microsoft Imagine (formerly called Dreamspark) to get a lot of valuable software and other useful resources (such as a free, three-month subscription to Pluralsight). Another free program is Microsoft Visual Studio Dev Essentials, and yet another free program is Microsoft IT Pro Cloud Essentials.

In future posts, I’ll talk about some other hardware options for a home lab.

 

 

 

Operating System Support for SQL Server Versions

There are currently six major versions of SQL Server that I commonly see being used in Production, along with five major versions of Windows Server. Only certain combinations of SQL Server and Windows Server are officially supported by Microsoft, but tracking down this information is a little tedious.

Table 1 shows these possible combinations and whether they are officially supported by Microsoft. One possibly surprising combination is the fact that SQL Server 2012 is not officially supported on Windows Server 2016. Perhaps this is less surprising if you keep in mind that SQL Server 2012 will fall out of mainstream support on July 11, 2017, which is not that far away.

 

Picture1

Table 1: OS Support for Recent Versions of SQL Server

 

The available links that document this are listed below:

Hardware and Software Requirements for Installing SQL Server 2008 R2

Hardware and Software Requirements for Installing SQL Server 2012

Hardware and Software Requirements for Installing SQL Server 2014

Hardware and Software Requirements for Installing SQL Server (for 2016 and later)

 

If you are getting ready to deploy a new instance of SQL Server 2014 or SQL Server 2016, then you should prefer Windows Server 2016, even though they are also supported on older operating systems. If you are getting ready to deploy a new instance of SQL Server 2008 through SQL Server 2012, then you should prefer Windows Server 2012 R2, even though they are supported on older operating systems.

Finally, if you are getting ready to deploy a new instance of SQL Server 2005, then I feel a little sorry for you! SQL Server 2005 is out of extended support, and it is missing so many useful features that were added in newer versions of SQL Server.

Actually, I recently helped a client deploy some new instances of SQL Server 2005 for some pretty valid business reasons. We ended up deploying to a VM (on new, very fast host hardware) that was running Windows Server 2008 R2, which worked perfectly fine.

 

SQL Server Diagnostic Information Queries for January 2017

This month, I have updated the legacy queries for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. There are also minor updates for the SQL Server 2012 and newer queries.

Rather than having a separate blog post for each version, I have just put the links for all six 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 Server vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries (January 2017)

SQL Server nNext Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries (January 2017)

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries (January 2017)

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries (January 2017)

SQL Server 2012 Blank Results Spreadsheet

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

Since SQL Server 2008 R2 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.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

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 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 three related Pluralsight courses, which are 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 three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three 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!

Intel Speed Shift Support in Windows Server 2016

If you are gathering evidence to help make the case for a complete data platform upgrade in 2017, you want to find as much information as possible to bolster your argument. This post is meant to assist you in your efforts.

By early-mid 2017, Windows Server 2016 will have been GA long enough to convince most skeptics that it is safe and stable. Windows Server 2016 has many tangible advantages over previous versions of Windows Server, which I will be discussing in future blog posts.

SQL Server 2016 already has its first Service Pack available, with many very valuable enhancements (especially for Standard Edition). Some people who are on older versions of SQL Server Enterprise Edition may be able to migrate to SQL Server 2016 Standard Edition SP1.

Finally, Intel is due to release the next generation two-socket server processor (which will require new model servers from the server vendors). One specific new improvement you will get with a new server/processor and Windows Server 2016 is called Intel Speed Shift.

Intel Speed Shift (which is different than the older Intel SpeedStep technology) was added in the Intel Skylake microarchitecture. It requires operating system support in order to work, and the first OS to enable Speed Shift was Windows 10, after an update in November 2015. This feature lets the OS give control of processor P-states back to the hardware, with P-state requests coming from the operating system. This lets the processor increase the clock speed of individual cores much more quickly in response to an OS request for more performance.

The upcoming Intel Xeon E5-2600 v5 processors (Skylake-EP) for two-socket servers are due to ship in mid-2017 (although rumor has it that they are already shipping to some cloud data center providers). I don’t know for sure whether Intel Skylake-EP will have Speed Shift like the mobile and desktop Skylake processors do, but it probably will. Supposedly, the flagship Xeon E5-2699 v5 will have 32 physical cores, even though you will likely want a lower core count model with a higher base clock speed in most cases, to minimize your SQL Server licensing costs.

Why this matters for SQL Server is that many short-duration OLTP queries might run a little faster with Speed Shift enabled. Legacy power management techniques on older processors can take up to 120ms to fully throttle up to full Turbo Boost clock speed, while Speed Shift does it in 35ms, with most of the frequency increase happening in 3-4ms.

Figure 1 shows an example of the difference in how quickly the processor can increase the clock speed with Speed Shift Technology.

 

Figure 1: Intel Speed Shift Technology Performance Example

 

If your operating system supports Intel Speed Shift (Windows 10 or Windows Server 2016) and if you have an Intel processor with Speed Shift Technology (Skylake or newer), then you will be able to get the performance benefits of Intel Speed Shift with virtually no effort beyond perhaps changing a BIOS/UEFI setting on your server. Figures 2 and 3 have some more information about how Intel Speed Shift works.

 

Image result for intel speed shift

Figure 2: Intel Speed Shift Technology Introduction

 

Image result for intel speed shift

Figure 3: Intel Speed Shift Technology Details

 

Over the past year, there has been a decent amount of information regarding Intel Speed Shift support in Windows 10, but nothing regarding Windows Server 2016. Part of the problem here is that we already have Skylake processors available in the mobile and desktop space, and in the single-socket server space, but not on the higher socket count server platforms.

Just as a quick experiment, I installed Windows Server 2016 Standard Edition on a brand new HP Spectre X360 laptop that has an Intel Core i7-7500U Kaby Lake-U processor, to see if Intel Speed Shift would be enabled or not. As you can see in Figure 4, the green SST lettering in the Features box on the left side of the System Summary screen in HWiNFO64 shows that Intel Speed Shift is enabled in Windows Server 2016, at least for this processor (which is the next release after Skylake).

 

clip_image001

Figure 4: Intel Speed Shift Enabled on Windows Server 2016 Standard Edition

 

Now, this is not definitive proof that Intel Speed Shift will be enabled with two-socket Skylake-EP processors, but I would say the chances are pretty good (especially given some conversations I have had with some people who actually know the answer)…

The outstanding question is how much will this actually help SQL Server performance? At this point, we simply don’t know, without actually doing some testing. Based on some reading I have been doing about Windows 10, I believe there is a way to temporarily disable Intel Speed Shift in Windows 10. Once I figure that out, it should not be that difficult to do some initial testing with short duration queries (150 ms or less) to see if they have lower average durations when Intel Speed Shift is enabled or not.

There are many other variables to consider regarding your workload and configuration that will likely affect the results in real production usage, but I expect there will be some positive benefit from this feature. As I like to say in presentations, nobody has ever told me that their database server is “too fast”, so I will take any performance improvement that I can find!