SQL Server Missing Indexes Feature and Trace Flag 2392

Background

Since the SQL Server 2005 release, SQL Server has had the default ability to collect metrics about what the query optimizer thinks are “missing” row store indexes, i.e. indexes that the query optimizer thinks would reduce the cost of particular individual queries that have been executed since SQL Server was last restarted.

The word missing is a little confusing to some people, since I have had many questions over the years about what could have happened to these “missing” indexes (implying that they used to be there, but somebody must have dropped them). Perhaps a better word would have been desired or candidate indexes.

At any rate, while far from perfect, I have found this feature to be a very useful method for helping to get the appropriate row store indexes in place for the overall SQL Server workload on a database. If you blindly add every new index that the query optimizer asks for, you will quickly have a large number of similar, narrow indexes on a table that probably could have been consolidated into fewer, wider indexes.

Using your common sense and experience as a DBA, your knowledge of your overall database workload and by running some queries to better characterize the volatility of a table, it is possible to do a pretty good job of index tuning using this feature. You also want to be on the lookout for indexes that can be dropped as part of your index tuning efforts.

These “missing indexes” are visible as the green missing index warnings in the graphical execution plan for a query, you can also find them by querying the plan cache looking for missing index warnings, and by running DMV queries to find them.


Recent Hotfix Reveals Trace Flag 2392

Microsoft has released a hotfix for the latest round of cumulative updates for SQL Server 2014, SQL Server 2016, and SQL Server 2017 that revealed trace flag 2392. This same fix is in SQL Server 2014 SP2 CU8, SQL Server 2016 SP1 CU6 and SQL 2017 CU2.

FIX: Access violation may occur when you cancel a pending query if the missing indexes feature is enabled in SQL Server 2014

Just to be clear, this hotfix has nothing to do with how the missing index feature functions. It just reveals a previously undocumented trace flag (TF 2392) that can be used to turn the missing index feature off completely (as a workaround to the issue that is corrected by the hotfix). This trace flag has been in the product since SQL Server 2005.

Once I discovered this trace flag, I was curious as to how it actually behaves, which is somewhat different than I expected. First, it is a startup-only trace flag, in terms of what it actually does. You can enable/disable it to your hearts content while SQL Server is running, and it will show as being enabled/disabled (with no error message about only being a startup-only TF).

The problem is, it will not disable/enable missing index stats collection unless it is enabled at startup. If you set it as a startup TF and restart SQL Server, then no missing index stats are collected. If you then subsequently disable TF 2392 while SQL Server is running, it still won’t collect any missing index stats (despite what you may expect).

You have to remove the startup TF 2392 and then restart SQL Server for the change to actually take effect (both ways, enabling and disabling). This is from my testing on SQL Server 2017 CU2.

Personally, I don’t think this is a big deal (unless you really need to enable the workaround described in the KB article). I think most people are going to want to leave the missing index feature enabled. The performance overhead of the feature is probably pretty insignificant.

You just need to be aware of how TF 2392 actually works and know that a restart of SQL Server is required to actually turn the missing index feature off or back on. It would be nice if you did get a message about it being a startup-only trace flag if you tried to enable/disable it while SQL Server was running.

SQL Server Diagnostic Information Queries for December 2017

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. There is also a new query for the SQL Server 2017 set of queries. 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 eight 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, and 2014:

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

 

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

Since SQL Server 2012 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.

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results Spreadsheet

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

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

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

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

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

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

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

Performance and Stability Fixes in SQL Server 2017 CU Builds

As of November 28, 2017, there have been two Cumulative Updates (CU) for SQL Server 2017. There have been a large number of hotfixes in each of these cumulative updates. If you are running on SQL Server 2017, I really think you should be running the latest SQL Server 2017 Cumulative Update as soon as you can test and deploy it.

You can follow the KB article link below to see all of the CU builds for the SQL Server 2017.

SQL Server 2017 Build Versions

Like I have done for other versions of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates for SQL Server 2017, 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 2017 features you are using.

Here are the fixes, by Cumulative Update, for SQL Server 2017:

SQL Server 2017 Cumulative Update 1 (Build 14.0.3006.16), 71 total public hot fixes

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

FIX: Portability and performance differ between Windows and Linux scheduler mappings in SQL Server 2017

FIX: A divide-by-zero error occurs when a parallel query is forced to run in a serial mode in SQL Server 2017

FIX: Remote instance of SQL Server crashes while executing a stored procedure that bulk loads an incomplete data file into a temporary table

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

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

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

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

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

FIX: Memory corruption occurs during availability group failovers for DTC transactions in SQL Server 2017

FIX: FORCE_LAST_GOOD_PLAN recommendation is stuck in “Verifying” state upon first verification in SQL Server 2017

FIX: Change Tracking manual cleanup fails with table non-existence error in SQL Server 2014 and 2017

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 or 2017

FIX: SQL Server Managed Backup does not delete old backups that are beyond the retention period in SQL Server 2014 or 2017

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

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 or 2017

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

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

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

FIX: Error occurs on passing memory-optimized table into inline table-valued function when called from stored procedure in SQL Server 2017

FIX: SQL Server 2017 reports that all transactions are completed even though some transactions encounter failures while trying to commit

FIX: An assertion error occurs within minutes or hours after you create a snapshot backup for a database that contains memory-optimized tables in SQL Server 2017

FIX: A deadlock condition occurs when you create a new database in SQL Server 2017

Update to enable SQL Server 2017 Express edition to act as a witness role in a database mirroring session

FIX: Automatic tuning settings cannot be configured on ModelDB in SQL Server 2017

Update to enable the new dynamic management views and functions in SQL Server 2017

Enhancement: New keyword is added to CREATE and UPDATE STATISTICS statements to persist sampling rate for future statistics updates in SQL Server

SQL Server 2017 Cumulative Update 2 (Build 14.0.3008.27), 33 total public hot fixes

Update for manual change tracking cleanup procedure in SQL Server 2017

Update adds a new extended event “marked_transaction_latch_trace” in SQL Server 2017 on Linux and Windows

Performance improvement for Spatial Intermediate Filter in SQL Server 2017

FIX: Excessive PREMPTIVE_OS_CREATEDIRECTORY waits during a workload that compiles or recompiles memory-optimized tables or table-types

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

FIX: Access violation when you cancel a pending query if the missing indexes feature is enabled in SQL Server

FIX: Error when you backup database with memory-optimized tables in SQL Server 2017

FIX: “sys.dm_exec_query_profiles” DMV returns wrong “estimate_row_count” in SQL Server 2017 on Linux and Windows

FIX: Deadlock when multiple PREDICT T-SQL functions run concurrently

FIX: Using ALTER TABLE on an in-memory optimized table crashes SQL Server 2017

FIX: SQL Server 2017 cannot decrypt data encrypted by earlier versions of SQL Server by using the same symmetric key

FIX: Data retrieval queries using non-clustered index seek take much longer in SQL Server

FIX: ForceLastGoodPlan recommendation state is falsely reported as Expired if it is applied manually in SQL Server 2017

FIX: “Message 611” error when you use BULK INSERT or INSERT SELECT to insert data into a clustered columnstore index

FIX: A parallel query execution plan that contains a “merge join” operator takes longer to execute in Cumulative Update 3, 4 or 5 for SQL Server 2016 Service Pack 1

FIX: Errors 33111 and 3013 when you back up a TDE encrypted database in SQL Server

FIX: Fatal error when a natively compiled stored procedure is executed to access memory-optimized tables in SQL Server 2017

FIX: Error 213 when you merge or split a partition of a partitioned graph table in SQL Server 2017 on Linux or Windows

FIX: Error 8624 when you execute a query that contains a SELECT DISTINCT statement on a graph column in SQL Server 2017 on Linux or Windows

Improvement: General improvements to the change tracking cleanup process in SQL Server 2017

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

SQL Server 2017 CU2 Is Available

On November 28, 2017, Microsoft released SQL Server 2017 CU2, which is build 14.0.3008.27.  I count 33 hotfixes in this CU. What is interesting is that there is a new column in the fix list called Platform, which will have a value of All, Linux, or Windows to indicate which platform the fix is relevant for. There are six Linux-only fixes, ten Windows-only fixes, with the remaining 17 fixes being for all platforms (Linux and Windows).

Keep in mind that with the Modern Servicing Model (MSM) that will be used for SQL Server 2017, there will be no Service Packs, and there will be monthly Cumulative Updates for the first twelve months after GA. After the first twelve months, there will be quarterly CUs for the next four years.

Microsoft has also changed their previous commitment to release CUs during the week of the 3rd Tuesday of the month of release. Going forward, they will just be released sometime during the month. This is fine with me, since I would rather see a solid release rather than one rushed out the door to hit a specific week of the month.

In my experience, checking the SQL Server 2017 build versions page is the best way to find out when a new CU has been released. You can also just follow me on Twitter, since I always tweet a link when a new CU is released.


Recent TPC-E Results on SQL Server 2017

Lenovo has submitted the two most recent TPC-E OLTP benchmark results, both using SQL Server 2017 running on Windows Server 2016 Standard Edition, using 28-core Intel Xeon Platinum 8180 processors.

The most recent result was for a four-socket Lenovo ThinkSystem SR950 with 3TB of RAM using a 48TB initial database size. This system had an official result of 11,357.28, which is the highest score ever submitted for a four-socket server. This system has a total of 112 physical cores, so if you divide the total score of 11,357.28 by 112, you get a measure of the single-threaded performance of the Intel Xeon Platinum 8180 processor under a full load (where the clock speed of the individual cores will be pretty close to the 2.5GHz base clock speed). In this case, the result is 101.40 score/core.

Back on June 27, 2017, Lenovo submitted a result for a two-socket Lenovo ThinkSystem SR650 with 1.5TB of RAM using a 28.5TB initial database size. This system had an official result of 6,598.36, which is the highest score ever submitted for a two-socket server. This system has a total of 56 physical cores, so if you divide the total score of 6,598.36 by 56, you get a score/core of 117.83, which is significantly higher than the result for the Lenovo ThinkSystem SR950 configured to use four-sockets (using the exact same Intel Xeon Platinum 8180 processor).

I would attribute most of this difference to the added NUMA overhead from a four-socket system, compared to a two-socket system. Another difference, which probably hurt the score of the two-socket system was the fact that it had to be running on a pre-release version of SQL Server 2017, based on the submission date of the benchmark.

This is just another piece of evidence that even with NUMA, capacity does not scale in a linear fashion as you add sockets to a server. Assuming you can split your workload across multiple database servers rather than just one, having two, two-socket servers instead of one, four-socket server will give you both more CPU capacity and better single-threaded CPU performance even when using the exact same model processor.

I would also argue that you could purposely pick a lower core count, but higher base clock speed processor from the same Intel Xeon Scalable Processor Family to find a sweet spot for SQL Server 2017 usage, where you have fewer physical cores to license, with better single-threaded performance across a higher number of servers.




Initial CrystalDiskMark Results for Intel Optane 900p

I have been building a new desktop workstation based on an AMD Ryzen Threadripper 1950X processor (which I will be describing in much more detail in a subsequent blog post). I am planning on using one of the brand new 480GB Intel Optane SSD 900p PCIe cards as my boot drive. Initially, I installed Windows 10 Professional, Version 1709 on a pretty lackluster OEM 256GB Toshiba M.2 NVMe drive that I had lying around. My plan is to clone that drive to the Intel Optane 900p.

I also have a couple of 1TB Samsung 960 PRO M.2 NVMe cards in this machine, so I thought I would run a couple of quick CrystalDiskMark tests on the two drives. One thing to keep in mind is that CrystalDiskMark is not the best synthetic benchmark to use to show off the strengths of the Optane 900p.

Traditional NAND-based SSDs excel at very high queue depths that are not usually encountered outside of synthetic benchmarks (especially for random read performance). Optane 900p SSDs perform extremely well for random reads at low queue depths. This gives you outstanding responsiveness and performance where it is going to be most noticeable in daily usage.

You can see part of this effect in the bottom row of CDM test results for reads, where the Optane 900p is doing about 4.3X more 4K IOPS than the Samsung 960 PRO at a queue depth of 1. A better test for this will be Microsoft DiskSpd, which can also measure the latency during the test run.

Here are some of the primary advantages of the Intel Optane 900p compared to current NAND flash storage.

 

    • High random read and write performance
    • High performance at low queue depths
    • High simultaneous read and write performance
    • High read and write performance at small capacity points
    • High performance maintained as the drive fills with data
    • Higher endurance than current NAND technology

 

image

Figure 1: 1TB Samsung 960 PRO with Samsung NVMe driver

 

image

Figure 2: 480GB Intel Optane 900p with Intel NVMe driver

 

SQL Server Diagnostic Information Queries for November 2017

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 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!

Presenting Two Sessions at the PASS Summit 2017 in Seattle

I’ll be presenting two sessions at the PASS Summit 2017 in Seattle, WA. There will be a half-day session called Migrating to SQL Server 2017 on Wednesday, November 1 in Room 6A, starting at 3:15PM, and then a regular session called Improving SQL Server Performance on Thursday, November 2 in Room 612, starting at 4:45PM.

Here are the abstracts:

Migrating to SQL Server 2017

How do you design and implement a safe and successful migration from an older version of SQL Server to SQL Server 2017 with no data loss and virtually no downtime? What if you have a limited hardware budget for the upgrade effort and you are worried about the core-based licensing in SQL Server 2017? How can you choose your hardware wisely in light of the new licensing model? How can you convince your organization that the time is right to upgrade to SQL Server 2017? This session will cover several different methods for migrating your data to SQL Server 2017 while meeting these objectives and minimizing your hardware and licensing costs. You will also learn how to help make the case that a complete data platform upgrade makes excellent sense from a business perspective. You will also learn how to do effective capacity planning for the new environment to ensure that you have the best performance possible with a manageable SQL Server 2017 licensing cost.

 

Improving SQL Server Performance

Has anyone ever told you that your SQL Server database or instance was running too fast? Probably not, but it is all too common to hear complaints about poor performance. As a database professional, you need to have the right tools and techniques for finding and correcting performance bottlenecks at all layers of your SQL Server instance. Starting with your hardware and storage, including virtualization, operating system considerations, instance-level configuration settings, database properties, and concluding with index and query tuning, we will cover all of the layers of common performance bottlenecks. You will learn detailed and practical information for improving SQL Server performance at every layer in the system.

 

My SQLskills colleague, Erin Stellato will also be at the PASS Summit 2017, presenting a pre-conference session called Solving Common Performance Problems Using Query Store on Monday, October 30, and then two regular sessions: Query Store and Automatic Tuning in SQL Server and Data Systems at Scale – A Panel Discussion on November 1 and November 2, respectively.

 

The PASS Summit is always a fun and useful conference, where you can connect and reconnect to people in the SQL Server Community. I hope to see you there, in my sessions and in the hallways and escalators!

Update: Just to make it easier to find, here are links to my Migrating to SQL Server 2017 session, Migrating to SQL Server 2017 demos, Improving SQL Server Performance session, and Improving SQL Server Performance demos.

 

 

 

SQL Server 2017 CU1 is Available

On October 24, 2017, Microsoft released SQL Server 2017 Cumulative Update 1, which is Build 14.0.3006.16. This CU has 68 hot fixes by my count, which is a fairly high count. This shows that Microsoft has been busy fixing issues that have shown up since the RTM build. I think this is a good thing.

Keep in mind that Microsoft has revised their servicing model for SQL Server 2017, with what they are calling the Modern Servicing Model for SQL Server, as I discussed here.

The gist of this new servicing model is that there will be no Service Packs for SQL Server 2017. Cumulative Updates will be released every month for the first year after GA, on the 3rd Tuesday of each month. After that, they will be released every three months, for the next four years, until SQL Server 2017 falls out of Mainstream support on October 11, 2022.

This means that hot fixes will be available more quickly during the first year, when most of the issues are discovered and corrected. You don’t have to install every single CU when it is released, but you should make a habit of looking at the fix list for each CU, to see if anything that might be affecting you is included. Once you do install the latest CU (which Microsoft recommends you do proactively), you will have all of the hotfixes since RTM, since they are actually cumulative in nature, as the name suggests.

 

 

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.