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.

CLR strict security

There is a new configuration option in SQL Server 2017 called CLR strict security and it is enabled by default.

Essentially when enabled, it treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE and any assemblies that are not signed will fail to load.

Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database.

-- to disable
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'clr strict security', N'0';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

-- to re-enable
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'clr strict security', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

Further information can be found here.

Quick look at resumable indexing in SQL Server 2017 CTP2.0

Allow for index to be paused:
ALTER INDEX NCI_c1 on t1 REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON);
 
View current state using:
SELECT * FROM sys.index_resumable_operations;
image1
Pause the index operation:
ALTER INDEX NCI_c1 on t1 PAUSE;
 
The state_desc now shows PAUSED:
SELECT * FROM sys.index_resumable_operations;
image2
To resume the index operation:
ALTER INDEX NCI_c1 on t1 RESUME;
 
To abort the index operation:
ALTER INDEX NCI_c1 on t1 ABORT;
 
Full command syntax and restrictions can now be found here

SQL Server vNext = SQL Server 2017

Yes SQL Server vNext officially has the name SQL Server 2017.
 
CTP 2.0 is now available to download and further details on the announcement can be found here
 
Self tuning databases? Sounds interesting or possibly a cause for concern. Hopefully these features have an off switch in case SQL starts optimizing in the wrong direction :)
 
“New in SQL Server 2017, we’re adding the Adaptive Query Processing family of intelligent database features. These features automatically keep database queries running as efficiently as possible without requiring additional tuning from database administrators.”
 
What does surprise me is the rate of new version releases.
 
Progress is good, but SQL Server 2012 -> 2014 -> 2016 -> 2017, this really starts to raise a dilemma for businesses/developers/DBAs as to which version should they be using/developing/training for. Throw Linux and the cloud into the mix and well it just gets even more interesting.
 
Businesses like stability and these new versions can introduce risk which is exactly what businesses want to avoid.
 
To me, this release cycle has to slow down or it could end badly for all parties. Although in saying that; DBAs would become more in demand as resolving incompatibility and version issues are likely to significantly increase.