SQL Server 2016 RC0

The news about Microsoft’s intention to “chum” SQL Server with Linux keeps floating around. But I haven’t heard a single word about SQL Server 2016 Release Candidate, that recently became available for download.

The release of SQL Server 2016 RTM is set for next month. So, in this overview I will cover some new features that will become available in new version on SQL Server, i.e. installation diffs, default TraceFlags, new functionality and the killer feature for analysis of execution plan.

Let’s start from installation of an instance of SQL Server 2016. In comparison with the previous version, the installer has undergone the following transformations:

• Only the 64-bit version of SQL Server is available for installation (the last x86 build has been cut off in CTP2.3). Here is the official statement: “SQL Server 2016 Release Candidate (RC0) is a 64-bit application. 32-bit installation is discontinued, though some elements run as 32-bit components”.

SQL Server 2016 does not support Windows 7 and Windows Server 2008. The official list of supported systems is as follows: all x64 editions of Windows 8/8.1/10 and Windows Server 2012.

SSMS is not provided with SQL Server and is developed separately. To download the standalone edition of SSMS, click this link. The new edition of SSMS supports SQL Server 2005…2016. Thus, you don’t need to keep the collection of SSMS’s for each version.

• There are two new components that implement the support for the R languages and PolyBase (bridge between SQL Server and Hadoop):

To work with PolyBase, you need to install JRE7, or a newer version:

And don’t forget to add the selected range of ports to the Firewall extensions:

I’m really grateful to Microsoft. Now you can turn on Instant File Initialization in the easy way during installation:

The dialog box for selection of default paths has been also changed a bit:

The separate tab for setting up tempdb has been introduced:

It allows to automatically set up the required number of files and, if required, to distribute them over different discs. But even without it, the Autogrowth parameter will be not 1Mb (as it used to be), but 64Mb by default.

Upon that, the maximal file size is limited to 256Mb. You can increase it, but it is possible only after installation:

At this point, installation diffs are over. Now, let’s take a look at other changes.

The settings of the model system database have been modified to minimize the number of AutoGrow events:

It is also worth mentioning that some TraceFlags on the new SQL Server will be enabled by default…

-T1118

SQL Server reads data from disc by parts 64Kb each (also known as extents). Extent is a group of eight physically consequent pages (8Kb each) of database files.

There are two types of extents: mixed and dedicated. A mixed extent may store pages from different objects. Such behavior allows very small tables to take up the minimum amount of space. But in most cases, tables go beyond 64Kb, and when more than 8 pages are required to store data for one object, the switch to allocation of dedicated extents takes place.

For initial allocation of dedicated extents for an object, TF1118 was provided. It was recommended to enable this trace flag. Therefore, it worked globally for all databases on the server.

It is not the case in version 2016. Now, you can set the MIXED_PAGE_ALLOCATION option for each user database:

As for system databases, this option is enabled by default, i.e. nothing has changed:

The exception has been made for user databases and tempdb:

Here is a small example:

MIXED_PAGE_ALLOCATION = ON:

MIXED_PAGE_ALLOCATION = OFF:

-T1117

Several files can be created within one file system. For instance, it is recommended to create several files for the tempdb database, that can increase system performance in certain scenarios.

Now, let’s put the case where all files being parts of the file group have similar size. One big temporary table is created. File #1 has sufficient space and, naturally, AutoGrow takes place. After a while, similar table is recreated, but insert is performed into file #2, since file #1 is temporary blocked. What will happen in this case? AutoGrow for file #2… and recurrent delay during query execution. For such cases, TF1117 was provided. TF1117 worked globally, and when one file was overloaded, it called AutoGrow for all files within one file group.

Now this trace flag is enabled for tempdb by default and can be set up selectively for user databases:

Let’s take a look at the file size:

Let’s create a temporary database:

There is not enough space to insert data and AutoGrow will take place.

AUTOGROW_SINGLE_FILE:

AUTOGROW_ALL_FILES:

-T2371

Before version 2016, the magic number “20% + 500 records” was used. Here is the example:

To refresh statistics, we need to modify the following:

In this case, it is 2500 rows, not at a time, but in general… this value is cumulative. First of all, let’s execute the following query:

Here is the result:

Statistics is old… Let’s perform one more query:

Hurray! Statistics has been updated:

Now, let’s suppose we have a large table, let’s say 10-20-30 million records. To recalculate statistics, we need to modify a massive range of data or to monitor the statistics update manually.

In SQL Server 2008R2 SP1, TF2371 was introduced. This trace flag understated that magic percent dynamically, depending on general quantity of records:

In SQL Server 2016, TF2371 is enabled by default.

-T8048

If your system includes 8 logical processes and a big number or waits for CMEMTHREAD and short-term blocks are expected:

the usage of TF8048 helped to get rid of performance issues. In SQL Server 2016, this trace flag is enabled by default.

SCOPED CONFIGURATION

The new group of settings was introduced at the database level:

You can get them from the new sys.database_scoped_configurations system view. As for me, I’m delighted with the fact that the parallelism level can be modified not globally as it used to be, but for each database individually:

Alternatives for previous versions are:
• enabling of the old Cardinality Estimation (previously you had to enable TF9481 or downgrade the Compatibility Level to 2012)

• disabling of Parameter Sniffing (previously you had to enable TF4136 or hardcode OPTIMIZE FOR UNKNOWN)

Also, the option to enable TF4199 has been introduced. It includes the massive list of all kinds of optimizations.

For those who like calling the DBCC FREEPROCCACHE command, the command for cleaning the procedure cache has been provided:

Alternative to the command is the following:

I think, that it will be useful to add a query allowing to track the volume of procedure cache with a breakdown into databases:

Now, let’s consider new functions:

JSON_MODIFY

In RC0, the option to modify JSON with help of the JSON_MODIFY has been added:

STRING_ESCAPE

Also, the STRING_ESCAPE function allowing to screen special characters in text has been added:

STRING_SPLIT

What a joy! Finally the STRING_SPLIT function has been delivered! It allows to get rid of previous perversions with XML and CTE:

But here is one sting: the function works only with a single-character delimiter:


Msg 214, Level 16, State 11, Line 3
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

In the context of productivity, let’s compare old split methods and new ones:

The execution results are the following:

Live Query Statistics

Another great thing is that new version of SSMS has the option to track query execution in a real-time mode:

This functionality is supported by not only SQL Server 2016, but by SQL Server 2014 as well. On the metadata level, this functionality is implemented through the sys.dm_exec_query_profiles query:

This goodie is quite cool. I know that Devart has already started to develop similar functionality in dbForge Studio.

To maintain readability of this overview, I withdrew some new features of SQL Server (Temporal Tables, Dynamic Data Masking and improvements in In-Memory), and intend to add them after the release of RTM version.

So, there we have it… Thank you for your attention.

Leave a Comment