Category: SQL Server 2017

SQL Server 2017 CU 9 Whats New

This is a bit late, but below are the changes detected between CU 8 and CU 9:

The view sys.dm_hadr_availability_replica_states has a new column called write_lease_remaining_ticks

The Microsoft documentation doesn’t mention this column yet, but the below new messages do give an idea on why it has been added.

The following messages have been added:

Message ID Severity Is Event Logged Text
47115 16 No The ‘%ls’ option is not valid for WSFC availability group ‘%.*ls’. Remove the option or set the ‘CLUSTER_TYPE’ option to a different value, then retry the operation.
47116 16 No The external lease cannot be set on availability group ‘%.*ls’. External Lease updates are not enabled for this availability group.
47117 16 No The ‘%ls’ option must be specified with a valid time value when updating the Availability Group’s external write lease status on availability group ‘%.*ls’.
47118 16 No The ‘%ls’ option must be specified with a valid value when updating the external lease status on availability group ‘%.*ls’.
47119 16 No The current write lease of the availability group ‘%.*ls’ is still valid. The lease expiration time cannot be set to an earlier time than its current value.

 

The following Module changes occurred:

sys.dm_hadr_availability_replica_states now includes a reference to the new write_lease_remaining_ticks

sys.sp_vupgrade_replsecurity_metadata has moved away from using the legacy dbo.sysdatabases and now uses sys.databases and excludes database snapshots from its check

New Old
CREATE VIEW sys.dm_hadr_availability_replica_states AS
SELECT
ars.replica_id,
ars.group_id,
ars.is_local,
ars.role,
ars.role_desc,
ars.operational_state,
ars.operational_state_desc,
ars.connected_state,
ars.connected_state_desc,
recovery_health = CASE
WHEN dbs.database_state_aggr IS NULL THEN NULL
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — ONLINE_IN_PROGRESS
WHEN dbs.database_state_aggr = 0 THEN CAST (1 AS TINYINT) — ONLINE
ELSE CAST (0 AS TINYINT) END, — ONLINE_IN_PROGRESS
recovery_health_desc = CASE
WHEN dbs.database_state_aggr IS NULL THEN CAST (NULL AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60))
WHEN dbs.database_state_aggr = 0 THEN CAST (‘ONLINE’ AS NVARCHAR(60))
ELSE CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60)) END,
synchronization_health = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (2 AS TINYINT) — Configuration-only, always healthy when connected
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (0 AS TINYINT) — NOT_HEALTHY
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — NOT_HEALTHY (one or more DBs not joined)
ELSE CAST (dbs.synchronization_health_aggr AS TINYINT) END,
synchronization_health_desc = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 2 THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 1 THEN CAST (‘PARTIALLY_HEALTHY’ AS NVARCHAR(60))
ELSE CAST (‘NOT_HEALTHY’ AS NVARCHAR(60)) END,
ars.last_connect_error_number,
ars.last_connect_error_description,
ars.last_connect_error_timestamp,
ars.write_lease_remaining_ticks
FROM
sys.dm_hadr_internal_availability_replica_states ars
LEFT OUTER JOIN
(
SELECT
replica_id,
database_count = COUNT (*),
synchronization_health_aggr = MIN (synchronization_health),
database_state_aggr = MAX (database_state)
FROM
sys.dm_hadr_database_replica_states
GROUP BY replica_id
)
AS dbs
ON (ars.real_replica_id = dbs.replica_id)
LEFT OUTER JOIN
(
SELECT
ag_replica_id,
availability_mode
FROM
sys.dm_hadr_internal_wsfc_ag_replicas
)
AS war
ON (ars.real_replica_id = war.ag_replica_id)		 CREATE VIEW sys.dm_hadr_availability_replica_states AS
SELECT
ars.replica_id,
ars.group_id,
ars.is_local,
ars.role,
ars.role_desc,
ars.operational_state,
ars.operational_state_desc,
ars.connected_state,
ars.connected_state_desc,
recovery_health = CASE
WHEN dbs.database_state_aggr IS NULL THEN NULL
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — ONLINE_IN_PROGRESS
WHEN dbs.database_state_aggr = 0 THEN CAST (1 AS TINYINT) — ONLINE
ELSE CAST (0 AS TINYINT) END, — ONLINE_IN_PROGRESS
recovery_health_desc = CASE
WHEN dbs.database_state_aggr IS NULL THEN CAST (NULL AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60))
WHEN dbs.database_state_aggr = 0 THEN CAST (‘ONLINE’ AS NVARCHAR(60))
ELSE CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60)) END,
synchronization_health = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (2 AS TINYINT) — Configuration-only, always healthy when connected
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (0 AS TINYINT) — NOT_HEALTHY
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — NOT_HEALTHY (one or more DBs not joined)
ELSE CAST (dbs.synchronization_health_aggr AS TINYINT) END,
synchronization_health_desc = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 2 THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 1 THEN CAST (‘PARTIALLY_HEALTHY’ AS NVARCHAR(60))
ELSE CAST (‘NOT_HEALTHY’ AS NVARCHAR(60)) END,
ars.last_connect_error_number,
ars.last_connect_error_description,
ars.last_connect_error_timestamp
FROM
sys.dm_hadr_internal_availability_replica_states ars
LEFT OUTER JOIN
(
SELECT
replica_id,
database_count = COUNT (*),
synchronization_health_aggr = MIN (synchronization_health),
database_state_aggr = MAX (database_state)
FROM
sys.dm_hadr_database_replica_states
GROUP BY replica_id
)
AS dbs
ON (ars.real_replica_id = dbs.replica_id)
LEFT OUTER JOIN
(
SELECT
ag_replica_id,
availability_mode
FROM
sys.dm_hadr_internal_wsfc_ag_replicas
)
AS war
ON (ars.real_replica_id = war.ag_replica_id)
CREATE PROCEDURE sys.sp_vupgrade_replsecurity_metadata
AS
BEGIN
SET NOCOUNT ON

DECLARE @retcode int,
@statement nvarchar(4000),
@database nvarchar(512),
@has_dbaccess bit,
@is_distributiondb bit,
@is_subscriberdb bit,
@is_upgraded bit
,@agentname nvarchar(100)
,@description nvarchar(100)
,@category_name sysname
,@dbname sysname

— Security Check
IF ISNULL(IS_SRVROLEMEMBER(‘sysadmin’), 0) = 0
BEGIN
— “Only members of the sysadmin fixed server role can perform this operation.”
RAISERROR(21089,16,-1)
RETURN 1
END

IF LOWER(DB_NAME()) != N’master’
BEGIN
— “sp_vupgrade_replsecurity_metadata can only be executed in the master database.”
RAISERROR (21482, 16, -1, ‘sp_vupgrade_replsecurity_metadata’, ‘master’)
RETURN 1
END

— make sure user has already upgraded msdb
IF NOT EXISTS(SELECT *
FROM msdb.sys.columns
WHERE object_id = OBJECT_ID(N’msdb.dbo.sysjobsteps’)
AND name = N’step_uid’)
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 16, -1, ‘security meta-data’, ‘all’, ‘msdb database’, ‘sp_vupgrade_replsecurity_metadata’)
RETURN 1
END

BEGIN TRANSACTION
SAVE TRANSACTION tr_upgrade_security_meta

DECLARE cursorDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT HAS_DBACCESS(name),
sys.fn_MSrepl_isdistdb(name),
N'[‘ + replace(name, N’]’, N’]]’) + N’]’,
name
FROM master.sys.databases
WHERE name NOT IN ( N’master’ COLLATE DATABASE_DEFAULT,
N’tempdb’ COLLATE DATABASE_DEFAULT,
N’msdb’ COLLATE DATABASE_DEFAULT,
N’model’ COLLATE DATABASE_DEFAULT
) and source_database_id is null — exclude DB snapshots
FOR READ ONLY

OPEN cursorDatabases

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname

WHILE @@FETCH_STATUS != -1
BEGIN
IF @has_dbaccess = 1
BEGIN
— if this is a distribution db then upgrade dist meta-data
IF @is_distributiondb = 1
BEGIN
SELECT @statement = N’SELECT @is_upgraded = 0 ‘ +
N’IF EXISTS(SELECT * ‘+
N’ FROM ‘ + @database + N’.sys.columns ‘ +
N’ WHERE name = N”job_step_uid”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_upgraded = 1 ‘ +
N’END’

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N’@is_upgraded bit OUTPUT’,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_upgraded = 1
BEGIN
SELECT @statement = @database + N’.[sys].[sp_MSupgrade_distdb_security_metadata]’

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 10, -1, ‘distributor security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
—
— Add replmonitor refresh job for this distribution database
—
select @agentname = formatmessage (20811, @dbname)
select @description = @agentname
,@statement = N’exec dbo.sp_replmonitorrefreshjob ‘
if not exists (select * from msdb.dbo.sysjobs_view where name = @agentname collate database_default and
upper(originating_server collate database_default) = upper(convert(sysname, SERVERPROPERTY(‘ServerName’)))
and master_server = 0)
BEGIN
select @category_name = name FROM msdb.dbo.syscategories where category_id = 18
EXECUTE @retcode = msdb.sys.sp_MSadd_repl_job @agentname,
@subsystem = ‘TSQL’,
@server = @@SERVERNAME,
@databasename = @dbname,
@description = @description,
@freqtype = 64, — continuous
@freqsubtype = 0, — unused
@freqsubinterval = 0, — unused
@command = @statement,
@retryattempts = 0,
@enabled = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name
if @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END

SELECT @statement = N’SELECT @is_subscriberdb = 0 ‘ +
N’IF EXISTS(SELECT * ‘ +
N’ FROM ‘ + @database + N’.sys.objects ‘ +
N’ WHERE name = N”MSsubscription_properties”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_subscriberdb = 1 ‘ +
N’END ‘ +
N’SELECT @is_upgraded = 0 ‘ +
N’IF EXISTS(SELECT * ‘ +
N’ FROM ‘ + @database + N’.sys.columns ‘ +
N’ WHERE name = N”job_step_uid”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_upgraded = 1 ‘ +
N’END’

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N’@is_subscriberdb bit OUTPUT, @is_upgraded bit OUTPUT’,
@is_subscriberdb = @is_subscriberdb OUTPUT,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_subscriberdb = 1
BEGIN
IF @is_upgraded = 1
BEGIN
— always assume this is subscriber db and attempt upgrade sub meta-data
SELECT @statement = @database + N’.[sys].[sp_MSupgrade_subdb_security_metadata]’

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 10, -1, ‘subscriber security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
END
END
ELSE
BEGIN
— “Could not open database %s. Upgrade of replication %s could not be performed. Please rerun %s from %s database once the %s is accessible.”
RAISERROR(21733, 10, -1, @database, ‘security meta-data’, ‘sp_vupgrade_replsecurity_metadata’, ‘[master]’, @database) WITH NOWAIT
END

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname
END

CLOSE cursorDatabases
DEALLOCATE cursorDatabases

COMMIT TRANSACTION tr_upgrade_security_meta

RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_upgrade_security_meta
COMMIT TRANSACTION

RETURN 1
END

 CREATE PROCEDURE sys.sp_vupgrade_replsecurity_metadata
AS
BEGIN
SET NOCOUNT ON

DECLARE @retcode int,
@statement nvarchar(4000),
@database nvarchar(512),
@has_dbaccess bit,
@is_distributiondb bit,
@is_subscriberdb bit,
@is_upgraded bit
,@agentname nvarchar(100)
,@description nvarchar(100)
,@category_name sysname
,@dbname sysname

— Security Check
IF ISNULL(IS_SRVROLEMEMBER(‘sysadmin’), 0) = 0
BEGIN
— “Only members of the sysadmin fixed server role can perform this operation.”
RAISERROR(21089,16,-1)
RETURN 1
END

IF LOWER(DB_NAME()) != N’master’
BEGIN
— “sp_vupgrade_replsecurity_metadata can only be executed in the master database.”
RAISERROR (21482, 16, -1, ‘sp_vupgrade_replsecurity_metadata’, ‘master’)
RETURN 1
END

— make sure user has already upgraded msdb
IF NOT EXISTS(SELECT *
FROM msdb.sys.columns
WHERE object_id = OBJECT_ID(N’msdb.dbo.sysjobsteps’)
AND name = N’step_uid’)
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 16, -1, ‘security meta-data’, ‘all’, ‘msdb database’, ‘sp_vupgrade_replsecurity_metadata’)
RETURN 1
END

BEGIN TRANSACTION
SAVE TRANSACTION tr_upgrade_security_meta

DECLARE cursorDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT HAS_DBACCESS(name),
sys.fn_MSrepl_isdistdb(name),
N'[‘ + replace(name, N’]’, N’]]’) + N’]’,
name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( N’master’ COLLATE DATABASE_DEFAULT,
N’tempdb’ COLLATE DATABASE_DEFAULT,
N’msdb’ COLLATE DATABASE_DEFAULT,
N’model’ COLLATE DATABASE_DEFAULT
)
FOR READ ONLY

OPEN cursorDatabases

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname

WHILE @@FETCH_STATUS != -1
BEGIN
IF @has_dbaccess = 1
BEGIN
— if this is a distribution db then upgrade dist meta-data
IF @is_distributiondb = 1
BEGIN
SELECT @statement = N’SELECT @is_upgraded = 0 ‘ +
N’IF EXISTS(SELECT * ‘+
N’ FROM ‘ + @database + N’.sys.columns ‘ +
N’ WHERE name = N”job_step_uid”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_upgraded = 1 ‘ +
N’END’

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N’@is_upgraded bit OUTPUT’,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_upgraded = 1
BEGIN
SELECT @statement = @database + N’.[sys].[sp_MSupgrade_distdb_security_metadata]’

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 10, -1, ‘distributor security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
—
— Add replmonitor refresh job for this distribution database
—
select @agentname = formatmessage (20811, @dbname)
select @description = @agentname
,@statement = N’exec dbo.sp_replmonitorrefreshjob ‘
if not exists (select * from msdb.dbo.sysjobs_view where name = @agentname collate database_default and
upper(originating_server collate database_default) = upper(convert(sysname, SERVERPROPERTY(‘ServerName’)))
and master_server = 0)
BEGIN
select @category_name = name FROM msdb.dbo.syscategories where category_id = 18
EXECUTE @retcode = msdb.sys.sp_MSadd_repl_job @agentname,
@subsystem = ‘TSQL’,
@server = @@SERVERNAME,
@databasename = @dbname,
@description = @description,
@freqtype = 64, — continuous
@freqsubtype = 0, — unused
@freqsubinterval = 0, — unused
@command = @statement,
@retryattempts = 0,
@enabled = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name
if @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END

SELECT @statement = N’SELECT @is_subscriberdb = 0 ‘ +
N’IF EXISTS(SELECT * ‘ +
N’ FROM ‘ + @database + N’.sys.objects ‘ +
N’ WHERE name = N”MSsubscription_properties”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_subscriberdb = 1 ‘ +
N’END ‘ +
N’SELECT @is_upgraded = 0 ‘ +
N’IF EXISTS(SELECT * ‘ +
N’ FROM ‘ + @database + N’.sys.columns ‘ +
N’ WHERE name = N”job_step_uid”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_upgraded = 1 ‘ +
N’END’

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N’@is_subscriberdb bit OUTPUT, @is_upgraded bit OUTPUT’,
@is_subscriberdb = @is_subscriberdb OUTPUT,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_subscriberdb = 1
BEGIN
IF @is_upgraded = 1
BEGIN
— always assume this is subscriber db and attempt upgrade sub meta-data
SELECT @statement = @database + N’.[sys].[sp_MSupgrade_subdb_security_metadata]’

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 10, -1, ‘subscriber security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
END
END
ELSE
BEGIN
— “Could not open database %s. Upgrade of replication %s could not be performed. Please rerun %s from %s database once the %s is accessible.”
RAISERROR(21733, 10, -1, @database, ‘security meta-data’, ‘sp_vupgrade_replsecurity_metadata’, ‘[master]’, @database) WITH NOWAIT
END

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname
END

CLOSE cursorDatabases
DEALLOCATE cursorDatabases

COMMIT TRANSACTION tr_upgrade_security_meta

RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_upgrade_security_meta
COMMIT TRANSACTION

RETURN 1
END

 

That’s all folks.

SQL Server 2017 CU8 has been released

With the release of CU8 (build 14.0.3029.16)  today, AM detected the following changes since CU7:

New Messages

Message ID Severity Is Event Logged Text
997 16 No Failed to get the update lock on database ‘%.*ls’.
12623 10 No Clone backup succeeded and is stored in %ls.
12624 10 No Clone backup failed.
12625 10 No RESTORE VERIFY failed on the clone backup %ls
12626 10 No Clone database verification has passed.
12627 10 No Clone database verification has failed.
12628 10 No NO_STATISTICS and NO_QUERYSTORE options turned ON as part of VERIFY_CLONE.
12629 10 No Database ‘%.*ls’ is a cloned database.
12630 16 No VERIFY_CLONE option cannot be specified together with SERVICEBROKER option.
33331 16 No DBCC CLEANUPCONVERSATIONS is not allowed on this server.
33332 16 No DBCC CLEANUPCONVERSATIONS cannot be executed through MARS connection.
41870 10 No Dropped %d Orphan Internal Table(s).

Further info on DBCC CLONEDATABASE can be found here.

Changed Message

Message ID New Text Old Text
12621 Database ‘%.*ls’ is a cloned database. This database should be used for diagnostic purposes only and is not supported for use in a production environment. Database ‘%.*ls’ is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.

Small wording change.

SQL Server 2017 sys.dm_db_xtp_checkpoint_internals

This new DMV is not currently documented, but below shows it in action:

--create test database
CREATE DATABASE d
GO
-- allow in-memory on database
ALTER DATABASE d ADD FILEGROUP [fg] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE d ADD FILE (NAME = f, FILENAME = N'C:\temp\') TO FILEGROUP [fg];
USE d;
--create test table
CREATE TABLE t (c1 int INDEX i1 NONCLUSTERED, c2 varchar(10)) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); 
--view sys.dm_db_xtp_checkpoint_internals
SELECT * FROM sys.dm_db_xtp_checkpoint_internals;
--create a checkpoint
CHECKPOINT;
--view sys.dm_db_xtp_checkpoint_internals
SELECT * FROM sys.dm_db_xtp_checkpoint_internals;
--create a checkpoint
CHECKPOINT;
--view sys.dm_db_xtp_checkpoint_internals
SELECT * FROM sys.dm_db_xtp_checkpoint_internals;

image1

SQL Server 2017 sys.query_store_wait_stats

sys.query_store_wait_stats is new to SQL Server 2017 and requires the query store as the name indicates.
 
Below shows a simple extract of the contained information.

SELECT 
 d.[text]
,CAST(b.query_plan AS xml) AS query_plan
,a.*
FROM sys.query_store_wait_stats a
INNER JOIN sys.query_store_plan b ON a.plan_id = b.plan_id
INNER JOIN sys.dm_exec_query_stats c ON b.query_plan_hash = c.query_plan_hash
CROSS APPLY sys.dm_exec_sql_text(c.[sql_handle]) d

image1

SQL Server 2017 sys.fn_full_dblog

There is a new DMF in SQL Server 2017 called sys.fn_full_dblog which is a more powerful alternative for the current sys.fn_dblog.
 
As shown below, sys.fn_full_dblog has several more parameters, but the returned columns are still the same.

--current sys.fn_dblog
SELECT TOP 10 * FROM sys.fn_dblog(null,null)
--SQL server 2017
SELECT TOP 10 * FROM sys.fn_full_dblog(null,null,DB_ID(),null,null,null,null,null)

image
The parameters available for sys.fn_dblog are:
image2
The parameters available for the new sys.fn_full_dblog are:
image3
With these extra parameters you can now target multiple databases etc.

SELECT a.[name] AS [database_name], COUNT(1) AS records
FROM sys.databases a
CROSS APPLY sys.fn_full_dblog(null,null,a.database_id,null,null,null,null,null) b
GROUP BY a.[name];

image4
NB: Both sys.fn_dblog and sys.fn_full_dblog are currently undocumented.