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

SQL Server Management Studio 17.0 released

It took a while, but finally SQL Server Management Studio 17.0 is available for download.
 
At 729MB the download exe is smaller than the 16.5.3 version which was 898MB. This huge size difference could mainly be due to the PowerShell module being moved into the PowerShell gallery. Just imagine attempting to install this via the good old 1.44 floppy disks.
 
It seems that these days to install SQL Server you’ll spend a lot of time trying to locate the pieces to install i.e. installation media + ssms + powershell + snac + patches etc, that all adds up to a lot of links. I kind of liked the old install where everything was in one place, but times are definitely a changing.
 
Extract from the SSMS download site:
•Over 100 connect issues fixed
•SQL Server PowerShell module moved out of SSMS and into the PowerShell gallery
•Icons updated to support high-DPI display modes
•Numerous performance improvements in Object Explorer
•WSUS support for upgrading to future 17.X versions
•Improved Multi-factor authentication support
•Enhancements to Availability Group functionality (Support for SQL Server on Linux, direct seeding, endpoint URL handling, and more)
•Showplan enhancements including new feature to analyze actual existing showplan to help diagnose issues
•SQL Server Analysis Server 1400 support
•New DAX Query Windows
•Numerous other enhancements for Power Query support

perfmon /sys

Just when you thought you knew everything about perfmon along comes /sys which allows saving your last configuration.
 
Kendra Little explains this great feature:

Handling divide by zero

In SQL Server when you divide by 0 you get an error.
image1
0 divided by a number works just fine.
image2
Back to the topic; handling divide by 0 is typically performed using CASE

--declare a table
DECLARE @t TABLE (c1 int, c2 int);
--insert some test data
INSERT INTO @t (c1, c2) VALUES (0,2);
INSERT INTO @t (c1, c2) VALUES (2,0);
--view the table
SELECT * FROM @t;
--handle a divide
SELECT
CASE WHEN c2 = 0 THEN 0 ELSE c1/c2 END AS result
FROM @t;

image3

SQL Server 2017 sys.dm_os_enumerate_fixed_drives

Now there is another way to identify free disk space in SQL Server 2017.

SELECT * FROM sys.dm_os_enumerate_fixed_drives;

image
What I don’t understand is why Microsoft doesn’t include the capacity of the drive. Not including the drive capacity requires the DBA to perform additional computations to extract the information.