Upgrading SQL Server– SQL Server 2016 Upgrade Considerations

One somewhat obscure issue you might encounter when upgrading from a legacy version of SQL Server to SQL Server 2016 or later is a change to how some data type conversions are handled. Microsoft describes it like this:

“Microsoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in several other operations. Most of these improvements offer increased precision when you deal with floating-point types, as well as with classic datetime types.”

This changed behavior only comes into play when you are running on database compatibility level 130 or later, and it only affects persisted objects (such as persisted computed columns, filtered indexes, indexed views, and indexes that use computed columns).

Because of this potential issue, Microsoft recommends that you do some additional validation checking on your legacy databases before you change their compatibility level to 130 or higher to find out if you might be affected by these changes.

Here are the steps for the validation and correction process:

  1. Restore the legacy database to SQL Server 2016 or later instance in your test environment
  2. Enable global trace flag 139
  3. Run DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
  4. Disable global trace flag 139
  5. Change the compatibility level of the database to 130 or later
  6. If Step 3 reveals any issues, then rebuild the objects that it identified

Microsoft has more detail about this change here. Personally, I think this just another step that you should add to your testing plan during an upgrade project.

 

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017. You can use this code: BL150GG to register for the PASS 2017 Summit to get a $150 discount.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server– Useful Improvements in SQL Server 2017

Even though much of the development effort and resources in SQL Server 2017 has been used to get SQL Server running as a first-class citizen on Linux, there are some interesting new features and improvements in SQL Server 2017 regardless of what operating system you are going to use.

One example are changes in the limits for tempdb initial file size for both tempdb data and log files. In SQL Server 2017, you can set the initial file size as large as 256GB (262,144MB) per file. If you set it to a size larger than 1GB and instant file initialization (IFI) is not enabled, you will get a warning during setup. Keep in mind that IFI only applies to SQL Server data files.

SQL Server 2017 also has a number of new DMV and DMFs that make it easier to manage and monitor your system. One example is sys.dm_db_log_info, which returns the virtual log file (VLF) count and other useful information about your VLFs. This is a more powerful replacement for the old DBCC LOGINFO command.

Another example (which I might be somewhat responsible for, due to much lobbying) are new columns in sys.dm_os_sys_info that reveal very useful processor information, including socket_count, core_count, and cores_per_socket. Getting this information form this DMV is much better that reading the SQL Server Error Log to to to find some of this information.

There are also nice improvements related to doing “smart” differential backups. The sys.dm_db_file_space_usage DMV has a new column, modified_extent_page_count which lets you track differential changes since the last full database backup in order to help decide whether it will be more efficient to take a full database backup instead of a differential backup.

For transaction log backups, there is a new DMF sys.dm_db_log_stats, which has a column called log _since_last_log_backup_mb. By checking the value of this column, you can programmatically decide when to take transaction log backups based on the amount of transaction log activity rather than just on time. This will let you take transaction log backups more frequently during periods of high activity (which can reduce how large the transaction log backup file is and also minimize transaction log file growth. It will also let you take transaction log backups les frequently during periods of low activity, which will reduce the number of transaction log backups that need to be restored, and possibly reduce your RTO times.

Small improvements like this, which are not always well publicized can really make your job easier as a DBA, which is yet another reason to push for an upgrade to SQL Server 2017.

 

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017. You can use this code: BL150GG to register for the PASS 2017 Summit to get a $150 discount.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server– Top Five Intel Xeon Scalable Processors for SQL Server Usage

On July 11, 2017, Intel formally released their new Xeon Scalable Processor Family of processors for data center usage. This new processor family has a somewhat ridiculous 58 different SKUs in the line. They are broken up into Platinum 8xxx, Gold 6xxx, Gold 5xxx, Silver 4xxx, and Bronze 3xxx sub-categories.

New two-socket server models(which are required for these processors), such as the Dell PowerEdge R740 let you choose from 41 of these available 58 processors (which is also somewhat overwhelming). Microsoft charges the same SQL Server core license prices regardless of the performance of each processor core. Given this, you need to try to pick a particular processor with an awareness of that fact to minimize your licensing costs.

Intel Xeon Bronze and Silver Processors

There are two choices in the Intel Xeon Bronze line, and six choices in the Intel Xeon Silver line. For SQL Server usage, you can and SHOULD ignore those eight processor SKUs from the Bronze and Silver lines. The Bronze processors have low base clock speeds, no Turbo Boost, no Hyper-Threading and low memory speeds. The Silver processors have low base clock speeds and low memory speeds.

Intel Xeon Gold and Platinum Processors

There are 22 different choices between the Intel Xeon Gold 5xxx and 6xxx lines, and 11 different choices in the Intel Xeon Platinum line. Out of these 33 different choices, I think five of them are the “best” choices for SQL Server usage, as measured by their single-threaded CPU performance. Even though these processors are more expensive than the Bronze and Silver lines, the extra hardware cost is a small fraction of the overall system cost, including SQL Server core licenses.

Top Five Intel Xeon Scalable Processors for SQL Server Usage

Here is my top five list, which is ranked by base clock speed and L3 cache size per physical core. Picking a processor from this list is going to give you the best performance for each one of your relatively expensive SQL Server 2016 Enterprise Edition core licenses, which cost $7,128.00 each.

  1. Intel Xeon Gold 5122 (4 cores)
  2. Intel Xeon Gold 6144 (8 cores)
  3. Intel Xeon Gold 6128 (6 cores)
  4. Intel Xeon Gold 6146 (12 cores)
  5. Intel Xeon Gold 6154 (18 cores)

Figure 1 shows my “preferred” processor models at each physical core count, along with some of their specifications and their price. Note that I have not included the “M” suffix processor models (which offer double the memory support for a $3,000.00 premium). Depending on your workload, you may want those for SQL Server usage, but I wanted to simplify the table somewhat.

You should also notice the huge premium that Intel charges for the highest core count SKUs, and think about whether that makes sense in your situation. If you can split your workload across multiple servers, you will be much better off from a performance and cost perspective with multiple servers with lower core count processors. Some choices are relatively bad (such as the Platinum 8170) due to their low base clock speeds.

Preferred Skylake-SP

Figure 1: Preferred Intel Skylake-SP Processors for SQL Server Usage

Figure 2 shows some comparative system metrics for a two-socket server with the given processor. These calculations are based on a single actual TPC-E benchmark score for a Lenovo ThinkSystem SR650 server using a pre-release version of SQL Server 2017. I suspect that we will see better results with the release version of SQL Server 2017.

Looking at these numbers, the Raw TPC-E Score is a measurement of the CPU capacity of the system, while Score/Core shows the single-threaded CPU performance for that particular processor. The License cost column shows how much it would cost to purchase SQL Server 2016 Enterprise Edition core licenses for that system.

The first thing jumps out to me is that the Platinum 8170 is a pretty bad choice compared to the Platinum 8168 or Gold 6154. Selecting the Gold 6154 instead of the Platinum 8170 would save you $114,048.00 in SQL Server license costs and $7,724.00 in hardware costs, with roughly the same capacity and much better single-threaded performance (which will be noticeable to your users).

Another good choice would be two database servers with the Gold 6146 instead of one database server with the Platinum 8180. This would save you $57,024.00 in SQL Server license costs and $6,874.00 in CPU costs (which would pay for a good chunk of the second server). Two servers would have more CPU capacity, twice the memory capacity, twice the number of PCIe 3.0 lanes, and much better single-threaded performance.

Comparative Skylake

Figure 2: Comparative Two-Socket System Metrics for Intel Skylake-SP Processors

 

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.

Here is a link to the complete series about upgrading SQL Server.