SQL Server 2016 RC0

I came across the second news on Habré that Microsoft will soon make friends with SQL Server and Linux . But not a word was said about the SQL Server 2016 Release Candidate , which became available for download just a few days ago.

Next month, RTM is planned to be released, so further analysis of some innovations will be available under the cut, which will be available in the new version: installation differences, default trace flags, new features and a killer feature for analyzing the execution plan.

Begin by installing an instance of SQL Server 2016 . The installer itself has changed since the previous version:
')

• Only the x64 version of SQL Server is available for installation (the last x86 build was cut out back in CTP2.3 ). Officially, everything sounds more concise: “SQL Server 2016 Release Candidate (RC0) is a 64-bit application. 32-bit installation is discontinued, though some elements run as 32-bit components. ”
• Installing SQL Server 2016 on Windows 7 and Windows Server 2008 is not supported. Official list of systems where you can install SQL Server : all x64 editions of Windows 8 / 8.1 / 10 and Windows Server 2012
• SSMS is no longer included with SQL Server and is being developed separately. Download standalone edition of SSMS at this link . The new edition of SSMS supports working with SQL Server 2005..2016 , so now you do not need to keep a whole fleet of studios for each version.
• Two new components have been added that implement support for the R language and PolyBase (a bridge between SQL Server and Hadoop ):

For PolyBase to work, you must first install JRE7 or a more recent version:



And then remember to add the selected port range to the Firewall exceptions:



Special thanks to Microsoft - now you don’t have to poke around in Group Policy to enable Instant File Initialization :



Also, the dialogue on the choice of default paths has changed a bit:



To configure tempdb, we made a separate tab on which you can automatically create the necessary number of files and post them, if necessary, across different disks. But even if you don’t do this, I ’m glad that with the default setting, the Autogrowth parameter will not be 1MB (as before), but 64MB .



At the same time, the maximum file size is limited to 256MB . You can set more, but after installation:



At this difference in installation compared with the previous version ends.

Now let's look at what else has changed ...

The model system database settings have changed to reduce the number of AutoGrow events:



Read why this is bad here .

It is also important to mention that some Trace Flags on the new SQL Server will be enabled by default ...

-T1118

SQL Server reads data from the disk in chunks of 64Kb (the so-called extents). An extent is a group of eight physically sequential pages ( 8Kb each) of database files.

There are two types of extents: mixed and homogeneous. On a mixed extent, pages from different objects can be stored. This behavior allows very small tables to occupy a minimal amount of space. But more often than not, tables are not limited to 64Kb in size and when more than 8 pages are required to store data on one object, then switching to the allocation of homogeneous extents takes place.

To initially allocate uniform extents for the object, TF 1118 was provided, which was recommended to be included. And it turned out that he worked globally for all databases on the server.

In the 2016 version of this will not be. Now for each user base, you can specify the MIXED_PAGE_ALLOCATION option:

ALTER DATABASE test SET MIXED_PAGE_ALLOCATION OFF 

For system databases, this option is enabled by default, i.e. everything remains as it was before:

 SELECT name, is_mixed_page_allocation_on FROM sys.databases 

An exception is made only for user databases and tempdb :

 name is_mixed_page_allocation_on ----------------- --------------------------- master 1 tempdb 0 model 1 msdb 1 DWDiagnostics 0 DWConfiguration 0 DWQueue 0 test 0 

I will give a small example:

 IF OBJECT_ID('dbo.tbl') IS NOT NULL DROP TABLE dbo.tbl GO CREATE TABLE dbo.tbl (ID INT DEFAULT 1) GO CHECKPOINT GO INSERT dbo.tbl DEFAULT VALUES GO SELECT [Current LSN], Operation, Context, AllocUnitName, [Description] FROM sys.fn_dblog(NULL, NULL) 

MIXED_PAGE_ALLOCATION = ON :



MIXED_PAGE_ALLOCATION = OFF :

-T1117

Within the same filegroup several files can be created. For example, for the tempdb database, it is recommended to create several files, which may in some scenarios increase system performance.

Now suppose the situation: all files included in the file group have the same size. A large temporary table is created. There is not enough space in file # 1 and of course AutoGrow happens . After a while, the same table is recreated, but the insertion occurs in file # 2 , because # 1 is temporarily locked. What then? AutoGrow for # 2 ... and repeated delay when executing queries. For such cases, TF 1117 was provided. He worked globally and, if there was not enough space in one file, he called AutoGrow for all files within one file group.

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

 ALTER DATABASE test MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES GO ALTER DATABASE test MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE GO 

Look at the size of the files:

 USE tempdb GO SELECT name , physical_name , current_size_mb = ROUND(size * 8. / 1024, 0) , auto_grow = CASE WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(10)) + '%' ELSE CAST(CAST(ROUND(growth * 8. / 1024, 0) AS INT) AS VARCHAR(10)) + 'MB' END FROM sys.database_files WHERE [type] = 0 

 name physical_name size_mb auto_grow ---------- --------------------------------------------------- -------- ------------ tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 8.000000 64MB temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 8.000000 64MB temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 8.000000 64MB temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 8.000000 64MB 

Create a temporary table:

 IF OBJECT_ID('#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t ( ID INT DEFAULT 1, Value CHAR(8000) DEFAULT 'X' ) GO INSERT INTO #t SELECT TOP(10000) 1, 'X' FROM [master].dbo.spt_values c1 CROSS APPLY [master].dbo.spt_values c2 

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

AUTOGROW_SINGLE_FILE :

 name physical_name size_mb auto_grow ---------- --------------------------------------------------- ----------- ------------ tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 72.000000 64MB temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 8.000000 64MB temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 8.000000 64MB temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 8.000000 64MB 

AUTOGROW_ALL_FILES :

 name physical_name size_mb auto_grow ---------- --------------------------------------------------- ----------- ------------ tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 72.000000 64MB temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 72.000000 64MB temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 72.000000 64MB temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 72.000000 64MB 

-T2371

Before the 2016 version, the magic number “20% + 500 lines” was used to automatically recalculate the statistics. Just show by example:

 USE [master] GO SET NOCOUNT ON IF DB_ID('test') IS NOT NULL BEGIN ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [test] END GO CREATE DATABASE [test] GO USE [test] GO IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL DROP TABLE dbo.tbl GO CREATE TABLE dbo.tbl ( ID INT IDENTITY(1,1) PRIMARY KEY, Value CHAR(1) ) GO CREATE NONCLUSTERED INDEX ix ON dbo.tbl (Value) GO INSERT INTO dbo.tbl SELECT TOP(10000) 'x' FROM [master].dbo.spt_values c1 CROSS APPLY [master].dbo.spt_values c2 

To update the statistics, you need to change:

 SELECT [>=] = COUNT(1) * .20 + 500 FROM dbo.tbl HAVING COUNT(1) >= 500 

In our case, this is 2500 lines. In this case, not at one time, but in general ... this value is cumulative. We perform the first request:

 UPDATE dbo.tbl SET Value = 'a' WHERE ID <= 2000 

We look:

 DBCC SHOW_STATISTICS('dbo.tbl', 'ix') WITH HISTOGRAM 

 RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ------------ ------------- ------------- -------------------- -------------- x 0 10000 0 1 

Statistics is old ... Perform another request:

 UPDATE dbo.tbl SET Value = 'b' WHERE ID <= 500 

Hooray! Statistics updated:

 RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ------------ ------------- ------------- -------------------- -------------- a 0 1500 0 1 b 0 500 0 1 x 0 8000 0 1 

Now suppose that our table is huge ... 10-20-30 million lines. In order to recalculate the statistics, we need to change an impressive amount of data or manually follow the statistics update.

Beginning with SQL Server 2008R2 SP1 , TF 2371 appeared, which dynamically underestimated that “magic” percentage depending on the total number of rows:

 < 25k = 20% > 30k = 18% > 40k = 15% > 100k = 10% > 500k = 5% > 1000k = 3.2% 

In SQL Server 2016, this tracing flag is enabled by default.

-T8048

In case there are more than 8 logical processors in your system and there is a large number of CMEMTHREAD expectations and short-term locks:

 SELECT waiting_tasks_count FROM sys.dm_os_wait_stats WHERE wait_type = 'CMEMTHREAD' AND waiting_tasks_count > 0 SELECT spins FROM sys.dm_os_spinlock_stats WHERE name = 'SOS_SUSPEND_QUEUE' AND spins > 0 

then using TF 8048 helped get rid of performance problems. In SQL Server 2016, this trace flag is enabled by default.

SCOPED CONFIGURATION

At the base level, a new group of settings has appeared :



You can get them from the new sys.database_scoped_configurations system view. Personally, I am very pleased that the degree of parallelism can be changed not globally as before, but can be customized individually for each database:

 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0 

To enable the old Cardinality Estimation (previously it was necessary to turn on the TF 9481 or lower the compatibility level until 2012):

 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON 

Disable Parameter Sniffing ( TF 4136 or hardcode OPTIMIZE FOR UNKNOWN was previously included)

 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF 

Also added the ability to include the TF 4199 , which combines an impressive list of a variety of optimizations.

 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON 

For those who like to call out to call the DBCC FREEPROCCACHE command, we have provided a command for cleaning the procedural cache:

 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 

Analogue commands:

 DECLARE @id INT = DB_ID() DBCC FLUSHPROCINDB(@id) 

I also think it would be useful to add a query by which you can track the volume of the procedural cache in the context of databases:

 SELECT db = DB_NAME(t.[dbid]), plan_cache_kb = SUM(size_in_bytes / 1024) FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t WHERE t.[dbid] < 32767 GROUP BY t.[dbid] ORDER BY 2 DESC 

Now consider the new features:

JSON_MODIFY

RC0 added the ability to modify JSON using the JSON_MODIFY function:

 DECLARE @js NVARCHAR(100) = '{ "id": 1, "name": "JC", "skills": ["T-SQL"] }' SET @js = JSON_MODIFY(@js, '$.name', 'Paul') -- update SET @js = JSON_MODIFY(@js, '$.surname', 'Denton') -- insert SET @js = JSON_MODIFY(@js, '$.id', NULL) -- delete SET @js = JSON_MODIFY(@js, 'append $.skills', 'JSON') -- append PRINT @js 

 { "name": "Paul", "skills": ["T-SQL","JSON"], "surname":"Denton" } 

STRING_ESCAPE

Also appeared function STRING_ESCAPE , which escapes special characters in the text:

 SELECT STRING_ESCAPE(N'JS/Denton "Deus Ex"', N'JSON') 

 ------------------------ JS\/Denton \"Deus Ex\" 

STRING_SPLIT

Urgently rejoice! Finally, the STRING_SPLIT function has appeared , which saves us from previous perversions with XML and CTE :

 SELECT * FROM STRING_SPLIT(N'1,2,3,,4', N',') 

 value --------- 1 2 3 4 

But there is also a "fly in the ointment", the function works only with a single character separator:

 SELECT * FROM STRING_SPLIT(N'1--2--3--4', N'--') 

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

In terms of performance, we compare the old split and new methods:

 SET STATISTICS TIME ON DECLARE @x VARCHAR(MAX) = 'x' + REPLICATE(CAST(',x' AS VARCHAR(MAX)), 500000) ;WITH cte AS ( SELECT s = 1, e = COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1), v = SUBSTRING(@x, 1, COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1) - 1) UNION ALL SELECT s = CONVERT(INT, e) + 1, e = COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1), v = SUBSTRING(@x, e + 1, COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1)- e - 1) FROM cte WHERE e < LEN(@x) + 1 ) SELECT v FROM cte WHERE LEN(v) > 0 OPTION (MAXRECURSION 0) SELECT tcvalue('(./text())[1]', 'VARCHAR(100)') FROM ( SELECT x = CONVERT(XML, '<i>' + REPLACE(@x, ',', '</i><i>') + '</i>').query('.') ) a CROSS APPLY x.nodes('i') t(c) SELECT * FROM STRING_SPLIT(@x, N',') 

Results of performance:

 (CTE) SQL Server Execution Times: CPU time = 18719 ms, elapsed time = 19109 ms. (XML) SQL Server Execution Times: CPU time = 4672 ms, elapsed time = 4958 ms. (STRING_SPLIT) SQL Server Execution Times: CPU time = 2828 ms, elapsed time = 2941 ms. 

Live Query Statistics

What else did you like ... In the new version of SSMS , it became possible to track how a request is being executed in real time:


This functionality is supported, not only in SQL Server 2016 , but also for SQL Server 2014 . At the metadata level, this functionality is implemented by selecting from sys.dm_exec_query_profiles :

 SELECT p.[sql_handle] , s.[text] , p.physical_operator_name , p.row_count , p.estimate_row_count , percent_complete = 100 * p.row_count / p.estimate_row_count FROM sys.dm_exec_query_profiles p CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) s 

To keep readability, I left behind some of the new features of SQL Server ( Temporal Tables , Dynamic Data Masking and In-Memory Improvements), which I plan to add after the RTM version is released.

If you want to share this article with an English-speaking audience:
SQL Server 2016 RC0