SQL101: AMD EYPC 7000 Series Processors

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

On June 20, 2017, AMD officially unveiled its new EPYC 7000 Series processor line for one and two-socket servers. These 14nm processors are based on the same Zen architecture as the recent AMD Ryzen desktop processors, with competitive single-threaded performance, along with very high core counts, memory density, and PCIe 3.0 lane counts.

These processors are a system on a chip (SoC) that includes CPU, memory controller, I/O controller and Server Controller Hub, so that no separate chipset is required. They have up to 32 physical cores per SoC, along with Simultaneous Multithreading (SMT), so you get 64 logical cores per SoC. You also get eight memory channels per socket, which means 16 DDR4 DIMMs per socket. This lets you have up to 2TB of RAM in a one-socket server, and 4TB of RAM in a two-socket server (with 128GB DIMMs). More realistically, you can easily and affordably have 512GB of RAM in a single-socket server with 32GB DIMMs.

You also get 128 PCIe 3.0 lanes per socket, which gives you a lot of total I/O capability. One very nice feature of these processors is that AMD does not cripple the lower-end SKUs when it comes to SMT, memory channels or PCIe 3.0 lanes, which is a big, welcome difference from how Intel does things with their product differentiation.

Another difference from Intel (which I actually don’t like) is that AMD does not have higher base clock speeds in their lower core count SKUs, so the existing strategy to reduce your SQL Server core license costs and also get better single-threaded performance by picking “frequency-optimized” low core count (LCC) processors, such as the Intel Xeon E5-2667 v4 is not going to work the same way.

What you can and should do, is to pick the fastest AMD EPYC SKU available at a given physical core count. For example, there are three EPYC 7000 SKUs that have 32 physical cores, the EPYC 7501, the EPYC 7551, and the EPYC 7601. Since the SQL Server core license cost will be the same, you should pick the EPYC 7601, to get the most performance possible for your license dollars.

Another important caveat is for SQL Server Standard Edition users. SQL Server 2016 Standard Edition has a license limit of four sockets or 24 physical cores, whichever is lower. This means that you need to be very careful what processor you choose for SQL Server 2016 Standard Edition. You basically have five choices to avoid exceeding these license limits.

You can get a 24-core EPYC 7401P, or a 16-core EPYC 7351P for a single-socket server. The 32-core EPYC 7551P would exceed your Standard Edition license limit. In a two-socket server, you can choose an eight-core EPYC 7251, and populate either one or two sockets. You can also choose a 16-core EPYC 7351 or a 24-core EPYC 7451 and just populate one socket of a two-socket server.

If you buy a new database server that has more than 24 physical cores, SQL Server 2016 Standard Edition will only use 24 physical cores per instance, but Microsoft will still expect you to pay for a core license for every physical core present in the machine. This could be a very expensive mistake. In a worst case scenario, you buy a two-socket server with two of the 32-core EPYC 7601 processor, and end up having to pay about $72K in extra license costs for cores that you are not allowed to use in a single instance.

Microsoft has not formally announced any change in these license limits for SQL Server 2017 Standard Edition, but hopefully they will raise these license limits to a more realistic value for modern processors from AMD (and for the upcoming Intel Skylake-SP processors).

SQL Server Diagnostic Information Queries for June 2017

This month, there are some minor updates to the all of the versions of the queries. I usually make additional minor updates to the queries 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 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 2017, 2016, 2014 and 2012:

SQL Server 2017 Diagnostic Information Queries

SQL Server 2017 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!

SQLskills SQL101: SQL Server Core Factor Table

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Back in the SQL Server 2012 release time-frame, Microsoft published a SQL Server Core Factor Table document that essentially provided a 25% discount for most AMD Opteron processors with six or more physical cores. This document was updated for the SQL Server 2014 release.

Even with this discount, it was not really cost-effective to use AMD Opteron processors for SQL Server usage, because of their extremely poor single-threaded performance. You could easily get more total capacity, better single-threaded performance, and lower SQL Server licensing costs with an appropriate, modern Intel Xeon E5 or E7 processor.

For the SQL Server 2016 release, there was no update for the SQL Server Core Factor Table. In fact, Microsoft has a useful new document, entitled “Introduction to Per Core Licensing and Basic Definitions” where they explicitly state that the Core Factor Table is not applicable to SQL Server starting with SQL Server 2016. So far, there has been no word of any change in this stance for SQL Server 2017.

Despite this, there is still some confusion and misinformation about the SQL Server Core Factor Table, such as this example:

Using the Core Factor Table

The SQL Server Core Factor Table is not necessary for SQL Server 2008 R2 (which used processor licensing instead of core licensing), and it does not apply to SQL Server 2016 and newer. It is only valid for SQL Server 2012 and SQL Server 2014.

It will be interesting to see whether the upcoming AMD Epyc “Naples” server processors will perform well with SQL Server workloads. They certainly will have enough memory density and PCIe 3.0 lanes to be very interesting for some types of SQL Server workloads, such as DW/Reporting. AMD is also pitching the idea that a single-socket server using an AMD Epyc processor will be a good alternative to a two-socket Intel server.

Guidance for WannaCrypt/WannaCry Attacks

There has been quite a bit of media coverage about the WannaCrypt/WannaCry ransomware over the past several days. Microsoft has a new page with information about this particular issue and steps that can be taken to protect your systems. I have also collected some more detailed background information about this and about SQL Server security patching in general.

Just to be clear, there is no known threat to SQL Server from this method, but there was an out of band security update for SQL Server 2012, 2014, and 2016 that was released on November 8, 2016. Here are the most current cumulative updates for SQL Server 2012, 2014, and 2016 (which will include that security update).

SQL Server 2012              SQL Server 2012 SP3 CU9            11.0.6598.0                      May 15, 2017

SQL Server 2014              SQL Server 2014 SP2 CU5            12.0.5546.0                      April 17, 2017

SQL Server 2016              SQL Server 2016 SP1 CU3            13.0.4435.0                      May 15, 2017

 

WannaCrypt/WannaCry Information

Here are some links to useful resources about this outbreak. Making sure your servers and client machines are current with their Microsoft Update hotfixes and possibly disabling SMB v1 are the best defenses.

Alert (TA17-132A) Indicators Associated With WannaCry Ransomware

Microsoft Security Bulletin MS17-010 – Critical

MS17-010: Description of the security update for Windows SMB Server: March 14, 2017

Windows Update Catalog Download Links

 

SMB v1 Information

Another mitigation measure for this vulnerability is to disable Server Message Block (SMB) v1 (which has been deprecated since Windows Server 2012). Depending on what version of Windows Server you are running, you may be able to do this using various methods.

The Deprecation of SMB1 – You should be planning to get rid of this old SMB dialect

Stop using SMB1

How to enable and disable SMBv1, SMBv2, and SMBv3 in Windows and Windows Server

 

SQL Server Security Update Information

Microsoft now recommends proactively installing SQL Server Cumulative Updates as they become available. The most recent, specific security update (MS16-136) for SQL Server 2012, 2014, and 2016 was released on November 8, 2016. If you are up to date with your SQL Server Service Packs and Cumulative Updates, you will already have that SQL Server security update. Just to be clear, there is no indication that SQL Server is vulnerable to WannaCry. It is merely a best practice to stay current with SQL Server security and other updates.

Announcing updates to the SQL Server Incremental Servicing Model (ISM)

Where to find information about the latest SQL Server builds

MS16-136: Security update for SQL Server: November 8, 2016

 

Finally, there are a number of other good reasons to make an effort to keep your SQL Server instances up to date with the latest Service Pack and Cumulative Update. I highlight some of the more important hotfixes for every cumulative update in the blog posts linked below:

Performance and Stability Related Fixes in Post-SQL Server 2012 SP3 Builds

Performance and Stability Related Fixes in Post-SQL Server 2014 SP1 Builds

Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds

Performance and Stability Related Fixes in Post-SQL Server 2016 SP1 Builds

SQL Server Diagnostic Information Queries for May 2017

This month, there are two new queries for SQL Server 2017, along with more minor updates for several of the SQL Server 2012 and newer version queries. I have also made some minor updates to the SQL Server 2005, 20087, and 2008 R2 queries. I usually make minor updates to the queries 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 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 2017, 2016, 2014 and 2012:

SQL Server 2017 Diagnostic Information Queries

SQL Server 2017 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!

SQLskills SQL101: Sequential Throughput

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

The Importance of Sequential Throughput for SQL Server

A number of very common, important operations that are often executed by SQL Server are potentially performance limited by the sequential throughput of the underlying storage subsystem. These include:

  1. Full database backups and restores
  2. Index creation and maintenance work
  3. Initializing transactional replication snapshots and subscriptions
  4. Initializing AlwaysOn AG replicas
  5. Initializing database mirrors
  6. Initializing log-shipping secondary’s
  7. Running DBCC CHECKDB
  8. Relational data warehouse query workloads
  9. Relational data warehouse ETL operations

Despite this, I often see DBAs having to contend with extremely low sequential performance on their various database servers, to the detriment of their ability to meet their SLAs for things like RPO and RTO (not to mention their sanity). This being the case, what if anything can you do to improve this situation?

One thing you should do is to do some storage subsystem benchmarking with tools like CrystalDiskMark and Microsoft DiskSpd, to find out what the potential performance of each logical drive is on the underlying machine where your SQL Server instance is running.

You can also run some simple queries and tests from SQL Server itself to see what level of sequential performance you are actually getting from your storage subsystem (which is much harder for storage administrators, SAN administrators, and storage vendors to dispute). One example is running a full database backup to a NUL device, to see the ultimate sequential read performance from where your data and log files are located. Another example is running a SELECT query with an index hint to force the query to do a clustered index scan or table scan from a relatively large table.

Note: You should do these kinds of tests during a maintenance window or ideally, before a new instance of SQL Server goes into Production. Otherwise, your testing could negatively affect your Production environment or the other Production activity could skew your test results.

Beyond that, here are some general steps you can take to improve overall storage system performance:

  1. Make sure you have power management configured correctly at all levels (BIOS power management, hypervisor power policy, and Windows Power Plan)
  2. Make sure you have Windows Instant File Initialization enabled
  3. Make sure you are not under memory pressure (to reduce stress on your storage subsystem)
  4. Make sure you are using the latest version of SQL Server
  5. Make sure you have installed the latest Service Pack and Cumulative Update for your version of SQL Server
  6. Favor Enterprise Edition or Standard Edition (because it has better I/O performance)
  7. Use compression to reduce your I/O requirements (backup compression, data compression, and columnstore indexes
  8. Make sure your indexes are tuned appropriately (not too many and not too few)
  9. Keep your index fragmentation under control

You can watch my Pluralsight course SQL Server: Improving Storage Subsystem Performance to get more details about this subject. You can also read my article on SQLPerformance.com, Sequential Throughput Speeds and Feeds to get some more technical details about sequential throughput.

SQLskills SQL101: Using DDL Triggers

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

DDL Triggers

One very useful safety feature that was added to the product with the release of SQL Server 2005 is Data Definition Language (DDL) triggers. Even though they have been available for quite some time, I still don’t see that many people actually using them on their systems, which I think is a shame.

DDL triggers are described by the online documentation like this:

DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.

Basically, when a T-SQL command does something that affects the metadata or schema of your database, you can capture and log some useful information about what was changed, what the change was, when it was changed, and who did it. Depending on how you configure the DDL trigger, you can capture things that actually are metadata changes (such as CREATE, ALTER or DROP) for things like tables, views, stored procedures, functions, and indexes. You can also capture things that I don’t really consider true metadata changes, such as an ALTER INDEX REORGANIZE command.

DDL Trigger Actions

Just like with a DML Trigger, you have to decide what happens when a DDL Trigger fires. For example, one action that Microsoft likes to use in their documentation is to simply have a ROLLBACK command, along with an error message that indicates what happened. This is designed to prevent someone (perhaps you) from accidentally making a terrible mistake such as dropping a table from a Production database.

DDL Trigger Usage

Another common usage is to simply log relevant information about all DDL changes that you decide to capture to a table that you create in each database. This can be very useful when multiple people have admin rights in your Production databases. Even if that is not the case, having a record of all DDL changes to a database can be very helpful.

Another use for DDL Triggers is to capture what is happening with your index maintenance. DDL commands such as ALTER INDEX REORGANIZE and ALTER INDEX REBUILD can be logged to help you analyze your index maintenance. For example, if you see the same index being reorganized or rebuilt on a frequent, regular basis, you might want to consider lowering the fill factor on that index to reduce how quickly it becomes fragmented, which will decrease how often it needs to be reorganized or rebuilt.

Conclusion

DDL Triggers can be very useful, and they are very easy to use for a number of different purposes. They are not as secure or as powerful as SQL Server Audit, but they are available in all editions of SQL Server, starting with SQL Server 2005. They are also much easier to set-up and use. I have an example of how to create a table to log some DDL changes, along with an actual DDL trigger to capture the changes, available here.

 

 

SQL Server 2014 SP2 Cumulative Update 5

Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 5, which is Build 12.0.5546.0. There are 24 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. My blog post below highlights some of the more important fixes in all of the post-SP2 Cumulative Updates:

Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds

Microsoft has also released SQL Server 2014 Service Pack 1 Cumulative Update 12, which is Build 12.0.4502.0. There are 12 hotfixes in the public fix list for this CU. My blog post below highlights some of the more important fixes in all of the post-SP1 Cumulative Updates:

Performance and Stability Related Fixes in Post-SQL Server 2014 SP1 Builds

There is no corresponding CU for the RTM branch, since SQL Server 2014 RTM is no longer a supported Service Pack level. If you are still on the RTM branch (which is not a good thing), then my blog post below highlights some of the more important fixes in all of the RTM Cumulative Updates:

Performance and Stability Related Fixes in Post-SQL Server 2014 RTM Builds

Finally, if you are wondering why this matters, you might want to read my SQLskills SQL101: SQL Server Maintenance post to find out more about how SQL Server is serviced by Microsoft and why staying current is important.

 

 

SQLskills SQL101: Processor Selection for SQL Server

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

 

SQL Server Licensing

Since SQL Server 2012, Microsoft has been using core-based licensing for SQL Server Enterprise Edition. With non-virtualized servers, you are required to purchase a SQL Server core license for every single physical processor core in the entire host machine, period. Every single physical core present in the host machine must be licensed. It doesn’t matter if you have disabled physical cores in the host BIOS, or if you have exceeded the physical core license limit for SQL Server Standard Edition, you still have to license every single physical core in the machine.

With virtualization, the story is slightly different. Normally, you have to purchase a SQL Server core license for every single vCPU in your virtual machine, with a minimum of four core licenses per VM. The exception to this is if you purchase enough SQL Server core licenses for all of the physical cores in the entire host machine, and if you also purchase Microsoft Software Assurance. If you do this, you can then create as many VMs with as many vCPUs as you like, without worrying about counting the vCPU cores at all.

Windows Server 2016 Licensing

Windows Server 2016 has a new core-based licensing model with a minimum of eight physical core licenses per processor and 16 physical core licenses per host machine. Fortunately, these Windows Server 2016 core licenses are relatively affordable, especially for Windows Server 2016 Standard Edition (which is all that is required for most SQL Server 2016 instances). The danger from this new licensing model is that it may encourage well-meaning server administrators to select a processor with more physical cores than they actually need for SQL Server, in order to “get their money’s worth” from the Windows Server 2016 licenses that they are required to buy for a new server. This could actually be a very expensive mistake from a SQL Server 2016 licensing cost perspective!

Modern Server Processors for SQL Server

Current generation Intel server processors have anywhere from four to twenty-four physical cores in each physical processor. For two-socket servers, this means the Intel Xeon E5-2600 v4 “Broadwell-EP” Product Family. For four-socket and higher servers, this means the Intel Xeon E7-8800 v4 “Broadwell-EX” Product Family. Upcoming server processors from both AMD and Intel will have up to thirty-two physical cores per physical processor.

Previously, I explained the relevant differences between physical sockets, physical cores and logical cores here. One important fact to keep in mind is that Microsoft does not care (for pricing purposes) whether a physical core is fast or slow. Regardless of the performance of the core, the per-core license cost is exactly the same.

Knowing this, you should purposely choose a particular processor SKU that has the best single-threaded performance possible for a given physical core count. A very common mistake I see is where a server administrator purposely selects a low-range or mid-range processor SKU (at a given core count) to save a small amount of money on the hardware. Quite often, they save far less than 1% of the total system cost, but give up anywhere from 20-40% of their single-threaded performance.

For any particular server processor product family, you have a range of available processor SKUs with different physical core counts and other relevant performance specifications, such as base clock speed, L3 cache size, and QPI speed. Generally speaking, the lower core count processors have much better single-threaded CPU performance than the higher core count processors from the same product family. Quite often, you can purposely pick a fast, lower core count processor to both get better single-threaded CPU performance and to save a huge amount of money on your SQL Server 2016 licensing costs.

Conclusion

The key takeaway here is that it is very important to do some thoughtful analysis of your available processor choices for a server when you are going to have a SQL Server workload. The worst thing you can do is to just let someone else (who may not fully understand how SQL Server licensing works) make the choice with no input from you. It is unfortunately all too easy to make a very bad choice that costs significantly more than it should and also gives up a lot of performance.

I have written a number of articles for SQLPerformance.com that get into much more detail on this subject.

 

 

Performance and Stability Related Fixes in Post-SQL Server 2016 SP1 Builds

As of September 18, 2017, there have been five Cumulative Updates (CU) for the Service Pack 1 branch of SQL Server 2016. There have been a large number of hotfixes in each of these cumulative updates. If you are running on the SQL Server 2016 SP1 branch (which you should be by now), I really think you should be running the latest SQL Server 2016 SP1 Cumulative Update.

Table 1 shows the SQL Server 2016 SP1 CU builds that have been released so far.

BuildDescriptionRelease Date
13.0.4411SP1 CU1January 18, 2017
13.0.4422SP1 CU2March 20, 2017
13.0.4435SP1 CU3May 15, 2017
13.0.4446SP1 CU4August 8, 2017
13.0.4451SP1 CU5September 18, 2017
   

Table 1: SQL Server 2016 SP1 CU Builds

You can follow the KB article link below to see all of the CU builds for the SQL Server 2016 RTM and SQL Server 2016 SP1 branches.

SQL Server 2016 Build Versions

Like I have done for other versions and branches of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates in the SP1 branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing is completely arbitrary on my part. You may come up with a completely different list, based on what specific SQL Server 2016 features you are using.

Here are the fixes in the SP1 branch:

SQL Server 2016 SP1 Cumulative Update 1 (Build 13.0.4411), 54 total public hot fixes

FIX: The “sys.dm_db_column_store_row_group_physical_stats” query runs slowly on SQL Server 2016

FIX: DMV sys.dm_os_spinlock_stats returns incorrect results after you install SQL Server 2016 RTM CU2

FIX: SQL Server crashes when you execute a spatial data query that has been compiled in SQL Server 2016

FIX: An Always On secondary replica goes into a disconnecting state

FIX: A memory leak occurs when SQL Server procedure cache consumes too much memory

FIX: DMV sys.dm_hadr_availability_replica_states returns an incorrect synchronization health state for a distributed availability group

FIX: Out of memory occurs when you use long Hekaton transactions in SQL Server 2016

FIX: The Target Recovery Time of a database set to a nonzero value causes an assertion and a lease timeout in SQL Server 2014 or 2016

Decrease in performance and “non-yielding scheduler” errors caused by unnecessary spinlocks in SQL Server

FIX: Latch Contention may occur on execution of parallel queries on database that has Snapshot Isolation enabled in SQL Server 2016

FIX: Cannot execute the DBCC CLONEDATABASE command on an instance of SQL Server 2016 SP1 after an in-place upgrade

FIX: SQL Server crashes when you execute the OPENJSON function in a contained database in SQL Server 2016

FIX: SQL Server runs out of memory when you query data from memory-optimized tables if Resource Governor is enabled

FIX: Error messages are logged if database mirroring is configured by Transact-SQL in SQL Server 2016 and no database activity occurs for more than 90 seconds

Update to enable changing the PolyBase service accounts without reinstalling the PolyBase feature in SQL Server 2016

FIX: Error when create a stored procedure that uses a synonym together with an index hint in SQL Server 2016

FIX: An assert error occurs when you insert data into a memory-optimized table that contains a clustered columnstore index in SQL Server 2016

FIX: Error 3628 when you create or rebuild a columnstore index in SQL Server 2016

FIX: An access violation occurs when you execute DBCC CHECKDB on a database in SQL Server 2016

FIX: A dump file is generated when you enable system-versioning on a table in SQL Server 2016

FIX: Kernel crash when you create a database after you drop a database that contains FILESTEAM data in SQL Server 2016

FIX: An assertion occurs when you bulk insert data into a table from multiple connections in SQL Server 2016

An error occurs when you use ODBC driver to retrieve sql_variant data in SQL Server 2014 or 2016

FIX: Automatic update of statistics feature doesn’t run after you truncate a table that has a clustered columnstore index

FIX: A query that contains a hint against a view that references at least one temporal table in a different database can generate a dump file in SQL Server 2016

 

SQL Server 2016 SP1 Cumulative Update 2 (Build 13.0.4422), 101 total public hot fixes

FIX: The change table is ordered incorrectly for updated rows after you enable change data capture for a Microsoft SQL Server database

FIX: Memory leak when you query sys.dm_sql_referenced_entities view in SQL Server 2014 or 2016

FIX: Changing the data type and then updating the table with more than 4,000 records causes database corruption

FIX: Cannot insert data into a table that uses a clustered columnstore index in SQL Server 2016

FIX: Incremental statistics runs with higher sample rate than regular statistics when statistics are created or updated in SQL Server 2014 or 2016

FIX: DMV sys.dm_hadr_availability_replica_states returns an incorrect synchronization health state for a distributed availability group

FIX: ALTER TABLE, ADD CONSTRAINT, and PRIMARY KEY statements do not detect a duplicate key in SQL Server 2016

FIX: Cannot install SQL Server R Services during an offline installation of SQL Server 2016 updates

Improvement: Enhance VDI Protocol with VDC_Complete command in SQL Server

FIX: Unable to rebuild the partition online for a table that contains a computed partitioning column in SQL Server 2016

FIX: Assertion error when dm_exec_query_statistics_xml is used in a query plan that contains certain operators in SQL Server 2016

FIX: Wrong number of rows returned in sys.partitions for Columnstore index in SQL Server 2016

Statistics are removed after rebuilding a specific partition of an partitioned aligned index on a partitioned table in SQL Server

FIX: On failover, the new secondary replica stops accepting transaction log records until the instance is restarted in SQL Server

FIX: Deadlock when you execute a query plan with a nested loop join in batch mode in SQL Server 2014 or 2016

FIX: SQL Server crashes when you execute a spatial data query that has been compiled in SQL Server 2016

FIX: An assert error occurs when you insert data into a memory-optimized table that contains a clustered columnstore index in SQL Server 2016

FIX: DBCC CLONEDATABASE doesn’t copy the runtime cache of the query store to the clone in SQL Server 2016 SP1

FIX: Distribution Agent fails for a SQL Server 2014 publisher and a SQL Server 2012 subscriber in Transactional Replication

FIX: Incorrect full-text keys are recorded for the rows that aren’t indexed correctly by a full-text index in SQL Server

FIX: Data type conversion error in a query that involves a column store index in SQL Server 2016

FIX: Error 2809 when you execute a stored procedure that takes a table-valued parameter from RPC calls in SQL Server 2014 or 2016

FIX: Deadlock causes deferred transaction on the secondary replica in an Always On environment

FIX: “Non-yielding Scheduler” condition when you parallel-load data into a columnstore index in SQL Server 2016

SQL Server crashes with an access violation when you use the TRY…CATCH construct for bulk copy

FIX: Rebuilding a nonclustered index to add columns by using CREATE INDEX with DROP_EXISTING=ON and ONLINE=ON causes blocking

FIX: DBCC CHECKFILEGROUP reports false inconsistency error 5283 on a database that contains a partitioned table in SQL Server

Update improves DMV sys.dm_server_services and sys.dm_os_sys_info in SQL Server 2016

FIX: A memory leak occurs when SQL Server procedure cache consumes too much memory

FIX: “Incorrect syntax for definition of the ‘default’ constraint” error when you add an arbitrary columnstore column in SQL Server 2016

FIX: Error when you add a NOT NULL column with default values to a non-empty clustered columnstore index in SQL Server 2016 Standard and Express edition

FIX: Memory leak when you run a query that you don’t have sufficient permissions for in SQL Server 2014 or 2016

FIX: Intra-query deadlock when values are inserted into a partitioned clustered columnstore index in SQL Server 2014 or 2016

FIX: Assert memory dump on a mirror server in SQL Server

FIX: Error 5262 when you execute DBCC CHECKDB on the primary replica in SQL Server 2012, 2014 or 2016

FIX: Availability databases in incorrect initializing/synchronizing state after failover of SQL Server 2014 or 2016 AlwaysOn availability group

FIX: More CPU consumption when many consecutive transactions insert data into a temp table in SQL Server 2016 than in SQL Server 2014

FIX: CREATE OR ALTER statement applied on a DDL trigger fails on the next execution in SQL Server 2016

Improvement: Enable SQL Server Managed Backup to back up databases that are larger than 50 GB to Microsoft Azure in SQL Server 2016

Improvement: Improves the query performance for SQL Server 2016 by changing the use of histograms on UNIQUE columns

FIX: The sys.column_store_segments catalog view displays incorrect values in the column_id column in SQL Server 2016

FIX: Memory is paged out when columnstore index query consumes lots of memory in SQL Server 2014 or 2016

FIX: An access violation occurs when you execute DBCC CHECKDB on a database in SQL Server 2016

FIX: Error 21050 when you remove a table that is not part of a publication in SQL Server 2014 or 2016

FIX: Queries that run against secondary databases always get recompiled in SQL Server

FIX: Out-of-memory errors when you execute DBCC CHECKDB on database that contains columnstore indexes in SQL Server 2014 or 2016

FIX: Error 3628 when you create or rebuild a columnstore index in SQL Server 2016

FIX: Significantly increased PAGELATCH_EX contentions in sys.sysobjvalues in SQL Server 2016

FIX: Incorrect query result when you use varchar(max) variable in the search condition in SQL Server 2014 or 2016

FIX: You cannot select any replica when you fail over from an availability group that’s in the resolving state

FIX: No automatic failover after database mirroring stops unexpectedly in SQL Server

FIX: TDE encrypted Databases go in suspect state during the recovery phase when you restart SQL Server

FIX: Bad query plan created on secondary replicas after FULLSCAN statistics update on primary replica in SQL Server 2016

FIX: Checkpoint files grow excessively when you insert data into memory-optimized tables in SQL Server 2016

 

SQL Server 2016 SP1 Cumulative Update 3 (Build 13.0.4435), 70 total public hot fixes

FIX: Query against sys.dm_db_partition_stats DMV is slow if the database contains many partitions in SQL Server 2016

FIX: Error occurs when you drop a subscription by using a non-sysadmin account in SQL Server

FIX: Access violation occurs when used SELECT TOP query to retrieve data from clustered columnstore index in SQL Server 2016

FIX: Large memory allocations of type MEMOBJ_XSTMT occur when you run the sys.dm_exec_query_stats script in SQL Server

FIX: Parallel redo causes high memory usage in SQL Server 2016 when it’s compared to SQL Server 2014 or earlier versions

FIX: SQL Server 2016 consumes more memory when columnstore index is reorganizeda>

FIX: Error when you execute DBCC CLONEDATABASE on an “Always Encrypted” enabled database in SQL Server 2016

Update enables DML query plan to scan query memory-optimized tables in parallel in SQL Server 2016

Intra-query deadlock on communication buffer when you run a bulk load against a clustered columnstore index in SQL Server 2016

FIX: Error 608 when a stored procedure that inserts rows into a temporary table on which a spatial index is created is run in SQL Server

FIX: Wrong number of rows returned in sys.partitions for Columnstore index in SQL Server 2016

FIX: An assertion occurs when you run an UPDATE statement on a clustered columnstore index in SQL Server 2016

FIX: A severe error occurs when you create a spatial index with the GEOMETRY_GRID or GEOGRAPHY_GRID option in SQL Server

FIX: SQL Server 2016 stops responding when the “Latch_Suspend_End” extended event is triggered incorrectly

FIX: Out-of-memory error when you run a query to access LOB columns through In-Memory OLTP in SQL Server 2016

FIX: DMV sys.dm_hadr_availability_group_states displays “NOT_HEALTHY” in synchronization_health_desc column on secondary replicas in SQL Server

Update reduces the execution frequency of the sp_MSsubscription_cleanup stored procedure in SQL Server

FIX: Deadlock when you use sys.column_store_row_groups and sys.dm_db_column_store_row_group_physical_stats DMV with large DDL operations in SQL Server 2016

FIX: Restore fails when you do backup with compression and checksum on TDE enabled database in SQL Server 2016

FIX: A memory leak in SQLWEP causes the host process Wmiprvse.exe to crash in SQL Server

FIX: Failed assertion and many access violation dump files after the sp_replcmds stored procedure is canceled in SQL Server

FIX: The sys.column_store_segments catalog view displays incorrect values in the column_id column in SQL Server 2016

FIX: Intra-query deadlock occurs when you execute parallel query that contains outer join operators in SQL Server 2016

FIX: Database schema is corrupted when you restore a database from a snapshot in SQL Server

 

SQL Server 2016 SP1 Cumulative Update 4 (Build 13.0.4446), 49 total public hot fixes

FIX: Deadlocks occur in SSISDB when you run multiple SSIS packages in SQL Server 2014 or 2016

FIX: Data mask on a floating points column is removed unexpectedly in SQL Server 2016

FIX: Deadlock when you use sys.column_store_row_groups and sys.dm_db_column_store_row_group_physical_stats DMV with large DDL operations in SQL Server 2016

FIX: “Non-yielding Scheduler” condition occurs when you change the BUCKET_COUNT value for large memory-optimized tables in Microsoft SQL Server 2016

FIX: An access violation occurs when you update a partitioned table that contains PERSISTED computed columns in SQL Server 2016

FIX: SUSER_SNAME function returns different result between SQL Server 2014 and SQL Server 2016

FIX: Transaction log backup failure on the secondary replica in SQL Server Always-On Availability Groups

FIX: An access violation occurs when you create an index with page compression in SQL Server

FIX: Parallel query execution returns incorrect results for merge join operations in SQL Server 2016

FIX: Access violation with query to retrieve data from a clustered columnstore index in SQL Server 2014 or 2016

FIX: “FILESTREAM feature is disabled” error when you use the FILESTREAM feature for SQL Server 2014 or 2016 in Windows 10 Creators Update

Fix: System generated stored procedures are created incorrectly in P2P publication if the schema name of published table contains a period (.) in SQL Server 2014 or 2016

FIX: Couldn’t truncate a partition of the partitioned table if it contains an extended or XML index in SQL Server 2016

FIX: A stored procedure may fail after an automatic failover occurs on a mirrored database in SQL Server

FIX: DMF sys.dm_db_incremental_stats_properties doesn’t show all partitions if partitioning column is set to character or binary data type

FIX: Assertion failure when backing up large TDE encrypted database in SQL Server

FIX: Access violation occurs when you execute a query in SQL Server 2016

FIX: SQL Server 2016 stops responding when the “Latch_Suspend_End” extended event is incorrectly triggered

FIX: Assertion error occurs on the secondary replica when you resume a suspended availability database in SQL Server 2016

FIX: Databases on secondary replica shows “NOT SYNCHRONIZING” status after failover in SQL Server 2016

FIX: “Incorrect syntax” error when you add a subscription by using the “sp_addpullsubscription_agent” stored procedure in SQL Server

FIX: “EXCEPTION_INVALID_CRT_PARAMETER” error with BULK INSERT statement in SQL Server 2016

FIX: Query with UNION ALL and a row goal may run slower in SQL Server 2014 or later versions when it’s compared to SQL Server 2008 R2

FIX: Memory leak occurs when you use memory-optimized tables in Microsoft SQL Server 2016 Standard edition

FIX: SQL Server 2014 or 2016 Backup to Microsoft Azure Blob storage service URL isn’t compatible for TLS 1.2

Update adds the “CLR strict security” feature to SQL Server 2016

FIX: Fail to compress the backup file when INIT and COMPRESSION option is used in a TDE enabled database in SQL Server 2016

FIX: Change Data Capture stops working after a recent cumulative update for SQL Server is installed

FIX: Restore fails when you do backup by using compression and checksum on a TDE enabled database in SQL Server 2016

 

SQL Server 2016 SP1 Cumulative Update 5 (Build 13.0.4451), 44 total public hot fixes

FIX: EXCEPTION_ACCESS_VIOLATION for query using sys.dm_os_memory_objects statement in SQL Server 2016

FIX: Access violation with query to retrieve data from a clustered columnstore index in SQL Server 2014 or 2016

FIX: Incorrect behavior when you use memory-optimized tables with “where exists” statement in SQL Server 2016

FIX: Automatic seeding in Availability Groups randomly causes error 41169 in SQL Server 2016

FIX: Log chain break in the “managed_backup.fn_available_backups” table in SQL Server 2016

FIX: Assertion error occurs on the secondary replica when you resume a suspended availability database in SQL Server 2016

FIX: Database mirroring failover fails with error 3456 in SQL Server 2016

FIX: Backup of availability database via VSS-based application may fail in SQL Server 2016

FIX: Timeout when you back up a large database to URL in SQL Server 2014 or 2016

FIX: Memory use with many databases greater in SQL Server 2016 than earlier versions

FIX: Constraint violation error returned by the managed_backup.fn_available_backups function after you install the Cumulative Update 2 for SQL Server 2016 SP1

FIX: Error message when you use the sp_execute_external_script stored procedure to insert data and specify @parallel value to 1 in SQL Server 2016

FIX: Access violation occurs when you query the sys.availability_groups catalog view in SQL Server 2016

FIX: Error when you configure an Azure SQL database to subscribe a transactional publication that contains spatial indexes in SQL Server 2016

FIX: DBCC CHECKFILEGROUP reports false inconsistency error 5283 on a database that contains a partitioned table in SQL Server

FIX: Couldn’t disable “change data capture” if any column is encrypted by “Always Encrypted” feature of SQL Server 2016

FIX: “Non-yielding Scheduler” condition occurs on spinlock contention in Microsoft SQL Server 2014 or 2016

FIX: Performance drop when using In-Memory OLTP with Always On availability groups in SQL Server 2016

FIX: Access violation occurs when a DDL trigger is raised by the CREATE EXTERNAL TABLE command in SQL Server 2016

FIX: Query that joins a view and contains UNION ALL slow in SQL Server 2016

FIX: Fails to resume a suspended availability database after a write error in SQL Server 2014 or 2016

FIX: Returns incorrect results when computed column is queried after installing hotfix that’s described in KB 3213683 and enabling TF 176 in SQL Server 2016

FIX: Indirect checkpoints on the tempdb database cause “Non-yielding scheduler” error in SQL Server 2016

FIX: SQL Server Managed Backups do not run a scheduled log backup in SQL Server 2016

Update to improve the performance for columnstore dynamic management views “column_store_row_groups” and “dm_db_column_store_row_group_physical_stats” in SQL Server 2016

 

The reason that I put these lists together is that I want to convince more people to try to keep their SQL Server instances up to date with Cumulative Updates. If you do the proper testing, planning and preparation, I think the risks from installing a SQL Server Cumulative Update are quite low (despite the occasional issues that people run into).

If you install a Cumulative Update or Service Pack on a Production system the day it is released, after doing no testing whatsoever, and then run into problems (and don’t have a plan on how to recover), then I don’t have that much sympathy for you.

On the other hand, if you go through a thoughtful and thorough testing process, and you have a plan for how you will install the CU, and how you would recover if there were any problems, then you are much less likely to have any problems. You are also much more likely to avoid the issues that are fixed by all of the included fixes in the new build of SQL Server. You have done your job as a good DBA.

Finally, Microsoft has changed their official guidance about whether you should install SQL Server Cumulative Updates. As they say, “we now recommend ongoing, proactive installation of CU’s as they become available”.