Windows Server Servicing Model Changes

Microsoft has announced some changes to the release schedule and servicing model for Windows Server. The new Semi-Annual Channel is a twice-per-year feature update release with an 18 month servicing timeline (meaning that Mainstream support ends 18 months after that Semi-Annual Channel release becomes available).

The current release in this channel is Windows Server, version 1709, which became available on October 17, 2017. This release will fall out of Mainstream support on April 18, 2019, and there is no Extended support period. In this new model, Windows Server releases are identified by the year and month of release: for example, in 2017, a release in the 9th month (September) would be identified as version 1709.

Microsoft describes the Semi-Annual Channel below:

“The Semi-Annual Channel provides opportunity for customers who are innovating quickly to take advantage of new operating system capabilities at a faster pace, both in applications – particularly those built on containers and microservices – and in the software-defined hybrid datacenter.”

You also have the option of staying on the Windows Server 2016 Long-Term Servicing Channel (LTSC) with the traditional five years of Mainstream support, five years of Extended support, and the option to purchase Premium Assurance, for six more years of support. The current release in this channel is Windows Server 2016, version 2016 which became available on October 12, 2016. This release will fall out of Mainstream support on January 11, 2022, and it will fall out of Extended support on January 11, 2027. The LTSC is scheduled to have new releases every two to three years.

The Semi-Annual Channel will be available to volume-licensed customers with Software Assurance, as well as via the Azure Marketplace or other cloud/hosting service providers and loyalty programs such as Visual Studio Subscriptions.

The Semi-Annual Channel can be installed as a Nano Server or Server Core, but is not available as Server with Desktop Experience. The Long-Term Servicing Channel can be installed as a Server with Desktop Experience or Server Core, but is not available as Nano Server.

 

Implications for SQL Server Usage

If you want to use the Semi-Annual Channel, you will have to be comfortable running SQL Server on Server Core (with no integrated GUI). You can either start using Powershell or you can use tools like Project Honolulu, which is a locally deployed, browser-based, management tool set that enables on-premises administration of Windows Servers with no Azure or cloud dependency.

The two most interesting new features for SQL Server in Windows Server, version 1709 are Storage-Class memory support for Hyper-V VMs and Virtualized Persistent Memory (vPMEM) for Hyper-V VMs.

Storage-class memory support for VMs enables NTFS-formatted direct access volumes to be created on non-volatile DIMMs and exposed to Hyper-V VMs. This enables Hyper-V VMs to leverage the low-latency performance benefits of storage-class memory devices. Virtualized Persistent Memory (vPMEM) is enabled by creating a VHD file (.vhdpmem) on a direct access volume on a host, adding a vPMEM Controller to a VM, and adding the created device (.vhdpmem) to a VM. Using vhdpmem files on direct access volumes on a host to back vPMEM enables allocation flexibility and leverages a familiar management model for adding disks to VMs.+

Virtualized Persistent Memory (vPMEM) is enabled by creating a VHD file (.vhdpmem) on a direct access volume on a host, adding a vPMEM Controller to a VM, and adding the created device (.vhdpmem) to a VM. Using vhdpmem files on direct access volumes on a host to back vPMEM enables allocation flexibility and leverages a familiar management model for adding disks to VMs.

Using Storage-class memory in a VM will let you use the Persisted Log Buffer feature (aka “tail of the log caching”) that was introduced in SQL Server 2016 SP1, as described here.

 

 

 

SQL Server Diagnostic Information Queries for October 2017

This month, I have added a set of diagnostic queries for Azure SQL Database. These are very much a work in progress, and they will be changing (and hopefully improving) quite a bit over the next few months

There are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the latest version is always a good idea.

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

Azure SQL Database Diagnostic Information Queries

Azure SQL Database Blank Results Spreadsheet

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 have made some updates to these queries periodically since January.

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results Spreadsheet

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I am not planning on moving these to Github any time soon.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my 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!

 

 

Posted in: DMV Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

Modern SQL Server Servicing Model

Microsoft has announced a pretty big change for how they are going to service SQL Server, starting with SQL Server 2017. I am very much in favor of the changes in this new model.

 

Background

Historically, Microsoft has used a combination of General Distribution Releases (GDRs), Cumulative Updates (CUs), and Service Packs (SPs) to update major SQL Server releases (which Microsoft refers to as servicing SQL Server). When a new major version of SQL Server reaches the general availability (GA) milestone, the servicing sequence for the original RTM branch begins. Typically, CUs are released every eight weeks, and they are a rollup of hotfixes and minor new features or feature improvements. GDRs only contain security hotfixes, and they are meant for organizations who want less software churn from SQL Server

Roughly a year after GA, Microsoft would release SP1 for that version of SQL Server. Organizations had the choice of staying on the RTM branch (which would continue to get CUs for about another year) or moving to the newer SP1 branch of the product (which would get its own CUs every eight weeks, and GDRs as needed). Roughly two years after GA, Microsoft would release SP2 for that version of SQL Server, which would start a new servicing branch with GDRs and CUs. The release of SP2 would halt the servicing of the RTM branch, which would then become an “unsupported SP”.

This has been the pattern since SQL Server 2005, so organizations should be pretty familiar with this old model.

 

Modern Servicing Model

The new servicing model will only use CUs and GDRs, there will be no more SPs. CUs will now have localized content (which was a big differentiator between CUs and SPs in the past). There will be a CU released every month for the first twelve months after GA, then changing to a new CU release every quarter for the next four years. New CUs will be delivered the week of the 3rd Tuesday of the month where they are released. About every twelve months, a slipstream-media CU will be released, which will let you install something like SQL Server 2017 with CU12 in one install instead of having to install SQL Server 2017 GA and then doing a separate install of SQL Server 2017 CU12.

Organizations will have to choose whether they will be on the “GDR Train” or the “CU Train”. If they are on the GDR Train and decide to install a CU, there is no going back to the GDR Train. Personally, I think it will be much better for most organizations to be on the CU Train, so that they get the benefit of hotfixes and feature improvements that will show up in CUs. Microsoft also advises organizations to proactively deploy CUs after they become available (after suitable internal testing).

You will need to be on a CU that is less than roughly 24 months old to be in a fully supported state from Microsoft (which is pretty similar to the current support window). Five years after GA, CU servicing will end as that version of SQL Server falls out of mainstream support, with only security fixes being available for the next five years, until that version of SQL Server falls out of extended support.

 

Premium Assurance 

After that, you can extend support for six more years with SQL Server Premium Assurance. Premium Assurance is somewhat pricey, with a sliding price scale that increases depending on when you purchase it. For SQL Server Enterprise Edition, it would currently cost $394.00 per core license, going up to $675.00 per core license if you purchased it after July 2019. Premium Assurance is available for SQL Server 2008 and later.

In most cases, I would much rather have moved to a newer version of SQL Server long before I ever had to think about using Premium Assurance, but for those situations where you have a mission critical legacy application that requires an ancient version of SQL Server, it is nice to at least have the option for a longer support period.

Microsoft’s official announcement about this change is covered in even more depth here.

 

SQLskills SQL101: Azure SQL Database Monitoring

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.

If you are ready to start working with Azure SQL Database, which is Microsoft’s Platform as a Service (PaaS) offering for SQL Server, you will want some good diagnostic queries to use to help you better understand how your databases are performing and to give you more information than is exposed by the Azure Portal. After much delay and procrastination on my part, I have finally developed a version of my SQL Server Diagnostic Information Queries that are tailored for use with Azure SQL Database.

Azure SQL Database Diagnostic Information Queries

Azure SQL Database 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.

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

SQL Server Diagnostic Information Queries for September 2017

On September 1, 2017, Dropbox turned off the sharing feature that I was previously using to host these scripts, which broke hundreds of existing links on my blog. I will be switching to their new sharing method, at least temporarily, until I decide what to do.

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the latest version is always a good idea.

Rather than having a separate blog post for each version, I have just put the links for all 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 have made some updates to these queries periodically since January.

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results Spreadsheet

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I am not planning on moving these to Github any time soon.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my 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!

Posted in: DMV Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

Upgrading SQL Server– Intel Xeon-W Family

On August 30, 2017, Intel released their new Intel Xeon Processor W Family of one-socket workstation processors (hence the W) based on the Skylake-SP Xeon server processors. These processors are meant as a replacement for the older one-socket Intel Xeon E5-1600 v4 series that had up to eight physical cores. The Xeon-W processors will have between four and eighteen physical cores, support up to 512GB of RAM and have 48 PCIe 3.0 lanes.

These processors are essentially Xeon versions of the Skylake-X Core i9 HEDT processors with all of the professional features enabled such as extended memory support, vPro, Intel’s AMT, and the standard enterprise Reliability, Serviceability and Availability (RAS) features. They will require a new C422 chipset, and they will not work in existing X299 chipset motherboards.

These processors give you another possible choice for buying or building a powerful, professional level workstation. Value-wise, a system based on one of the AMD Ryzen Threadripper processors is going to be a much better option for most people. Anandtech is a decent write-up here.

 

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– Database Experimentation Assistant

Another useful, if somewhat complicated, free tool from Microsoft is the Database Experimentation Assistant (DEA). Microsoft released DEA version 2.1 on July 25, 2017, with many new improvements over the earlier preview versions as detailed here.

Here is how Microsoft describes the DEA tool:

“Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server 2005 and above) to any new version of the SQL Server will be able to use these analysis metrics provided, such as queries that have compatibility errors, degraded queries, query plans, and other workload comparison data, to help them build higher confidence, making it a successful upgrade experience.”

Unlike the Database Migration Assistant (DMA) that I discussed previously, installing and using DEA is a little more involved. What this tool allows you to do is to record a production or at least production-like workload on one version of SQL Server than then replay that same workload on a different version of SQL Server (on a different server) to see how the new version performs with your data and workload. It could also be used to compare different hardware, storage, or different configuration settings.

The Microsoft Data Migration blog is a good source of addition information about both DMA and DEA.

 

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– Microsoft Data Migration Assistant 3.2

One useful tool that you should definitely take advantage of during an upgrade and migration effort is the Microsoft Data Migration Assistant (DMA), which replaces the old SQL Server Upgrade Advisor. Microsoft released the new DMA 3.2 on August 25, 2017.

This tool can be used for three main purposes. First, you can use it to find breaking changes, behavior changes, and deprecated features between a legacy version of SQL Server as the source (SQL Server 2005 and later) and a newer version of SQL Server as the target (SQL Server 2012, SQL Server 2014, SQL Server 2016, and Azure SQL Database).

Second, it can be used to identify new features in the target version of SQL Server (or Azure SQL Database) that might be useful given your legacy database schema and data size. Be forewarned that DMA has a heavy bias towards Azure SQL Database and other cloud-dependent features. For example, it is very eager to recommend Stretch database for large tables.

Finally, DMA can be used to actually migrate databases between legacy versions of SQL Server and a modern version of SQL Server or Azure SQL Database. An example of an assessment project is shown in Figure 1.

image

Figure 1: Microsoft Data Migration Assistant 3.2

If you are considering using DMA 3.2 to migrate existing on-premises databases to an Azure SQL Database, Microsoft’s Harini Gupta did some interesting benchmark testing showing how the Azure SQL Database’s service objective (S0, P1, P15, etc.) and the number of parallel tasks for the migration can affect the elapsed time for the migration.

 

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– 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.