SQL Server 2017 CTP 2.1 released

The release announcement is here.
 
Interestingly,
“In CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access to SQL Server Reporting Services is still included with your SQL Server license. The new installation process keeps our packages lean and enables customers to deploy and update Reporting Services with zero impact on your SQL Server deployments and databases.”
 
As mentioned previously, installs look to be getting a lot more complicated. I can see why Microsoft would do this for easier internal development, but for an end-user experience, this is likely not ideal.
 
On a side note ~11 months ago SQL Server 2016 CTP 2.1 was released :)

Pre-patching SQL Server

Before you apply a SQL Server patch, it is usually advised to perform the below:
1. Ensure that there are no user/application connections to the SQL Server instance.
2. Ensure there are no SQL agents jobs, that may kick off just before you start the patching.
3. If this is a Virtual Machine (VM), take a snapshot otherwise, ensure you at least have a backup of the system databases.
4. Restart the server. (This one step surprisingly usually resolves 99% of patch application issues and it’s not mentioned by Microsoft). Just stopping/starting the services or reviewing/resolving the locked files warning is typically less effective than a server restart.
 
Of course if you are using Database Mirroring, AlwaysOn, Clustering … there are other considerations, and typically these involve patching the Passive instance to minimize the outage window. Once again, restarting the passive server prior to patching usually ensures the smoothest patching experience.
 
Once patching is completed, it is also recommended to again restart the server. This final restart can give you the confidence that future restart issues are likely not related to this patch.
 
On none HA instances, these server restarts will likely increase the outage window, but it could prove to be the difference between a successful or failed patching attempt.

SQL Server Facets

You don’t often hear about references to Facets in SSMS, however this is a great feature which allows you to review and even change the SQL Server configuration.
images1
I often use it to quickly access the good old SQL Server 2005 Surface Area Configuration settings.
image2
Take a look at the various Facets available and you may be pleasantly surprised.
image3

Upgrading SQL Server on Linux

Surprisingly this appears to be a simple process.
 
Below shows an upgrade from CTP 1.4 to CTP 2.0 on Ubuntu.
image1
1. Run: sudo apt-get update
2. Run: sudo apt-get install mssql-server
image2
and that’s it.
image3
As the Linux offering progresses we’ll hopefully better understand any caveats regarding upgrades.
 
Further info on Linux installs, upgrades and uninstalls can be found here.

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

sys.dm_db_column_store_row_group_physical_stats

The DMV called sys.dm_db_column_store_row_group_physical_stats helps with reviewing column store indexes and especially deciding when the deleted rows (fragmentation) value is high.
 
Performance issues with this DMV were resolved in SQL Server 2016 SP1 CU1.
 
Columns returned are:
image1
Below is sample query to include the table and index name:

SELECT 
'[' + OBJECT_SCHEMA_NAME(a.[object_id]) + '].[' + OBJECT_NAME(a.[object_id]) + ']' AS table_name
,b.[name] AS column_store_index_name
,a.* 
FROM sys.dm_db_column_store_row_group_physical_stats a, sys.indexes b
WHERE 1=1
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id;

image2
Further info can be found here.