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.

SQL Server 2017 sys.dm_db_log_info

There is a new DMF called sys.dm_db_log_info which takes a single int parameter called @DatabaseId.
 
Below shows the output compared to the older DBCC LOGINFO and it makes vlf info a lot easier to review.

--old way
DBCC LOGINFO;

--SQL Server 2017
SELECT * FROM sys.dm_db_log_info(DB_ID(DB_NAME()));

image1
Below is an example of viewing multiple database vlf counts.

SELECT a.[name] AS [database_name], COUNT(b.database_id) AS vlfs 
FROM sys.databases a
CROSS APPLY sys.dm_db_log_info(a.database_id) b
GROUP BY a.[name];

image
Futher info can be found here.

Y and N vs 1 and 0

I suspect the Microsoft SQL development and change teams must be overrun at the moment.
 
That’s the only thing I could think of when I saw this in sys.dm_server_services:
image1
Since when did Microsoft start using uppercase Y and N instead of the usual 1 and 0?
 
It’s not a documentation error. How did that get through peer review and change control?
image2

New Azure SQL databases will be encrypted at rest by default

As mentioned here.
 
I suspect this raises an interesting question, who is responsible for the TDE keys, especially when it comes to long term-term backup retention as Microsoft automatically rotates these certificates at least every 90 days and I suspect the underlying server will change at some stage.
 
According to this it shouldn’t be an issue.
’10. Q: I have TDE enabled for my database. Can I use TDE with the vault?

A. Yes, TDE is supported. You can restore the database from the vault even if the original database no longer exists.

SQL Server 2017 modified_extent_page_count

Below shows an example of the new modified_extent_page_count column from the sys.dm_db_file_space_usage DMV in action. This will be really handy for large databases and deciding when to (or not) take differential backups.
 
As indicated, it only starts working after a Full backup, which makes sense :)
 
I’ve used SUM for the percentage example as sys.dm_db_file_space_usage reports on a file basis.

SET NOCOUNT ON;
--create database
CREATE DATABASE d1;
GO
--create table
USE d1;
CREATE TABLE t1 (c1 int, c2 varchar(10));
--insert some data
INSERT INTO t1 (c1,c2) VALUES (1,'one'),(2,'two'),(3,'three');
--look at modified_extent_page_count 
SELECT DB_NAME(database_id) AS db, modified_extent_page_count FROM sys.dm_db_file_space_usage;
--insert some more data
INSERT INTO t1 (c1,c2) VALUES (4,'four'),(5,'five'),(6,'six');
--look at modified_extent_page_count 
SELECT DB_NAME(database_id) AS db, modified_extent_page_count FROM sys.dm_db_file_space_usage;
-- backup database
BACKUP DATABASE d1 TO DISK = 'NUL';
--insert some more data
INSERT INTO t1 (c1,c2) VALUES (7,'seven'),(8,'eight');
--look at modified_extent_page_count 
SELECT DB_NAME(database_id) AS db, modified_extent_page_count FROM sys.dm_db_file_space_usage;
--to get percentage change 
SELECT
 DB_NAME(database_id) AS db
,SUM(modified_extent_page_count) AS modified_extent_page_count
,CAST((SUM(modified_extent_page_count) * 100.0/SUM(allocated_extent_page_count)) AS decimal(10,2)) AS '% change'
FROM sys.dm_db_file_space_usage GROUP BY database_id;

image1

Be careful with alias closure brackets in queries

As shown below, not closing alias brackets can cause SSMS column display readability issues and more importantly your query to not execute as expected.

--declare table
DECLARE @t TABLE (c1 varchar(10),c2 varchar(10));

--insert some data
INSERT INTO @t(c1,c2) VALUES ('one','two');
INSERT INTO @t(c1,c2) VALUES ('three','four');

--view data
SELECT 
 c1 AS [column1
,c2 AS [column2]
FROM @t;

image1
If you send the results to text, you can observe what is occurring and c2 is not considered in the query.
image2
The above was using the latest SSMS v17.0 version.

SQL Server 2017 STRING_AGG function

The STRING_AGG function has been introduced in SQL Server 2017.
 
Below shows a simple example of it in use to comma separate a column of data into a single row:

--declare table
DECLARE @t TABLE (c varchar(10));
--insert some data
INSERT INTO @t(c) VALUES ('one'),('two'),('three'),('four'),('five');
--view data
SELECT c FROM @t;
--old way
DECLARE @old varchar(100);
SELECT @old = COALESCE(@old + ',', '') + c FROM @t;
SELECT @old AS old_way;
--new way
SELECT STRING_AGG(c,',') AS new_way FROM @t;

image1