Delayed Durability Demonstration

-- Delayed Durability with a question
----------------------------------------------------------------------

Question:-

DB1

Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted
into DB1 or updated each second. Inserts are made by many different external applications that your
company’s developers do not control. You observe that transaction log write latency is a bottleneck in
performance. Because of the transient nature of all the data in this database, the business can tolerate some
data loss in the event of a server shutdown.

Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or
inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most
writes must have no tolerance for data loss in the event of a server shutdown. The business has identified
certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users
of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
You need to maximize performance of writes to each database without requiring changes to existing database
tables.
In the table below, identify the database setting that you must configure for each database.
NOTE: Make only one selection in each column. Each correct selection is worth one point.

Ans : explanation  below is on user database DelayedDurabilityTest.

 Key database properties for all databases on instance:-
-----------------------------------------------------------

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.containment_desc,
db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc,
db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,
db.is_auto_create_stats_incremental_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled,
db.is_published, db.group_database_id, db.replica_id,
db.is_memory_optimized_elevate_to_snapshot_on, db.delayed_durability_desc
FROM sys.databases AS db WITH (NOLOCK);


-- Get delayed durability setting for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
       db.delayed_durability_desc
FROM sys.databases AS db;

-- This is the default\legacy setting. All transactions are fully durable
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = DISABLED;

-- Any individual transaction can use Delayed Durability, controlled at the transaction level
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = ALLOWED;

-- All transactions use Delayed Durability. Transaction-level setting is ignored
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = FORCED;


-- Committing an explicit transaction in T-SQL with Delayed Durability
COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);





-- Control Transaction Durability
-- https://msdn.microsoft.com/en-us/library/dn449490(v=sql.120).aspx

-- Delayed Durability in SQL Server 2014
-- http://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014

-- Delayed Durability in SQL Server 2014
-- http://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/

Explanation:-
==============

USE [master];
GO

-- Drop database if it exists
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DelayedDurabilityTest')
ALTER DATABASE [DelayedDurabilityTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DelayedDurabilityTest];
GO

-- Create database if it does not exist
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'DelayedDurabilityTest')
BEGIN
CREATE DATABASE [DelayedDurabilityTest] CONTAINMENT = NONE
ON  PRIMARY (NAME = N'DelayedDurabilityTest', FILENAME = N'E:\SQLData\DelayedDurabilityTest.mdf',
SIZE = 1000MB, FILEGROWTH = 1000MB)
LOG ON (NAME = N'DelayedDurabilityTest_log', FILENAME = N'E:\SQLLogs\DelayedDurabilityTest.ldf',
SIZE = 100MB, FILEGROWTH = 1000MB)
END;
GO



USE DelayedDurabilityTest;
GO

CREATE TABLE dbo.TestData
(TestDataID int IDENTITY(1,1) NOT NULL,
TestDataDescription varchar(20) NOT NULL,
TestDataCounter int NOT NULL, CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED (TestDataID ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];
GO

-- All transactions are fully durable
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = DISABLED;


-- Test Run ***************************************************

-- Clear wait statistics
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

SET NOCOUNT ON;
DECLARE @CurrentIteration int = 0;
DECLARE @StartTime datetime = GETDATE();

WHILE (@CurrentIteration < 500000)
BEGIN
INSERT INTO dbo.TestData (TestDataDescription, TestDataCounter)
VALUES('Insert Test', @CurrentIteration);
SET @CurrentIteration += 1;
END


DECLARE @EndTime datetime = GETDATE();
DECLARE @ElapsedTime int = DATEDIFF(ms, @StartTime, @EndTime);

SELECT @ElapsedTime AS [Elapsed Time in ms];




-- Get wait statistics
WITH [Waits]
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
  (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
   signal_wait_time_ms / 1000.0 AS [SignalS],
   waiting_tasks_count AS [WaitCount],
   100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PREEMPTIVE_OS_AUTHENTICATIONOPS', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE',
N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND waiting_tasks_count > 0)
SELECT
MAX (W1.wait_type) AS [WaitType],
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
MAX (W1.WaitCount) AS [Wait Count],
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);


-- I/O Statistics by file for the current database
SELECT DB_NAME(DB_ID()) AS [Database Name], df.physical_name AS [Physical Name],
vfs.num_of_reads, vfs.num_of_writes,
CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read],
CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);

Observe Wait type and CPU utilization

-- 30296ms Fully Durable, 500059 writes to transaction log,  247.27MB written to transaction log

Now we will run above script using SET DELAYED_DURABILITY = FORCED;

USE [master];
GO

-- Drop database if it exists
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DelayedDurabilityTest')
ALTER DATABASE [DelayedDurabilityTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DelayedDurabilityTest];
GO

-- Create database if it does not exist
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'DelayedDurabilityTest')
BEGIN
CREATE DATABASE [DelayedDurabilityTest] CONTAINMENT = NONE
ON  PRIMARY (NAME = N'DelayedDurabilityTest', FILENAME = N'E:\SQLData\DelayedDurabilityTest.mdf',
SIZE = 1000MB, FILEGROWTH = 1000MB)
LOG ON (NAME = N'DelayedDurabilityTest_log', FILENAME = N'E:\SQLLogs\DelayedDurabilityTest.ldf',
SIZE = 100MB, FILEGROWTH = 1000MB)
END;
GO



USE DelayedDurabilityTest;
GO

CREATE TABLE dbo.TestData
(TestDataID int IDENTITY(1,1) NOT NULL,
TestDataDescription varchar(20) NOT NULL,
TestDataCounter int NOT NULL, CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED (TestDataID ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];
GO

-- All transactions use Delayed Durability
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = FORCED;

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

SET NOCOUNT ON;
DECLARE @CurrentIteration int = 0;
DECLARE @StartTime datetime = GETDATE();

WHILE (@CurrentIteration < 500000)
BEGIN
INSERT INTO dbo.TestData (TestDataDescription, TestDataCounter)
VALUES('Insert Test', @CurrentIteration);
SET @CurrentIteration += 1;
END


DECLARE @EndTime datetime = GETDATE();
DECLARE @ElapsedTime int = DATEDIFF(ms, @StartTime, @EndTime);

SELECT @ElapsedTime AS [Elapsed Time in ms];

-- 30296ms Fully Durable, 500059 writes to transaction log,  247.27MB written to transaction log


-- Get wait statistics
WITH [Waits]
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
  (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
   signal_wait_time_ms / 1000.0 AS [SignalS],
   waiting_tasks_count AS [WaitCount],
   100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PREEMPTIVE_OS_AUTHENTICATIONOPS', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE',
N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND waiting_tasks_count > 0)
SELECT
MAX (W1.wait_type) AS [WaitType],
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
MAX (W1.WaitCount) AS [Wait Count],
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);


-- I/O Statistics by file for the current database
SELECT DB_NAME(DB_ID()) AS [Database Name], df.physical_name AS [Physical Name],
vfs.num_of_reads, vfs.num_of_writes,
CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read],
CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);

-- You will observe wait type writelog is not present in the system. 

-- 6590ms  Delayed Durable, 14628 writes to transaction log, 176.27MB written to transaction log

In case if we do not want any data loss then do not go with DELAYED_DURABILITY, we should go with  ALLOW_SNAPSHOT_ISOLATION ON & SET READ_COMMITTED_SNAPSHOT ON



ALTER DATABASE [DelayedDurabilityTest]
SET ALLOW_SNAPSHOT_ISOLATION ON
go
ALTER DATABASE [DelayedDurabilityTest]
SET READ_COMMITTED_SNAPSHOT ON

hence for DB1 --

ALTER DATABASE [DB1] SET DELAYED_DURABILITY = FORCED;

and For DB2

ALTER DATABASE [DB2]
SET ALLOW_SNAPSHOT_ISOLATION ON
go
ALTER DATABASE [DB2]
SET READ_COMMITTED_SNAPSHOT ON

Thanks for Reading..

LVM Commands, Formatting, and Mounting the File System

Add a disk for use with SQL on linux

----------------------------------------------

from  HYPERV or VMWARE add 50 GB additional disk to linux server

$ lsblk

--partitioning

$pvcreate /dev/sdb1  

       

 -- v for volume creation

$vgcreate vg_data /dev/sdb1   

– l for logical disk creation

$lvcreate –n lv_data –extents 100%FREE vg_create     

Format the logical volume with a file system

---------------------------------------------------------

       xfs & ext4 are supported.

Command to format.

--------------------------------------

$ mkfs.xfs /dev/vg_data/lv_data

Mount the volume in your file system

-----------------------------------------------------------------

$ mount –t xfs /dev/vg_data/lv_data  /var/opt/mssql/data1

Persistently mount the volume in your file system

----------------------------------------------------------------

Go to /etc/fstab

$vi /etc/fstab

Insert below line

/dev/vg_data/lv_data /var/opt/mssql/data1 xfs defaults 0 0 

IO Related Dynamic Management Views and Functions

IO Related Dynamic Management Views and Functions

Find files that have percent growth-Key database properties for all databases on instance-Changing selected database properties

-- Find files that have percent growth
--------------------------------------------------------------------------------------------


SELECT DB_NAME([database_id]) AS [Database Name],
       [file_id], name, physical_name, type_desc, state_desc,
   is_percent_growth, growth,
   CONVERT(bigint, growth/128.0) AS [Growth in MB],
       CONVERT(bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE is_percent_growth = 1
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);


-- Key database properties for all databases on instance
------------------------------------------------------------------------
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.containment_desc,
db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc,
db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,
db.is_auto_create_stats_incremental_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled,
db.is_published, db.group_database_id, db.replica_id,
db.is_memory_optimized_elevate_to_snapshot_on, db.delayed_durability_desc   
FROM sys.databases AS db WITH (NOLOCK);



-- Changing selected database properties
--------------------------------------------------------------------------

USE [master]
GO

-- Enable auto update statistsics asynchronously
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;
GO

-- Enable delayed durability
ALTER DATABASE [AdventureWorks] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT;
GO

-- Enable CHECKSUM for the page verify option
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO


--These are the common settings which we should change

-- Get configuration values for instance
SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);


-- Set Instance-level options to more appropriate values

-- Enable backup checksum default
EXEC sys.sp_configure 'backup checksum default', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Enable backup compression default
EXEC sys.sp_configure 'backup compression default', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Change cost threshold for parallelism to a higher value
EXEC sys.sp_configure 'cost threshold for parallelism', X;
GO
RECONFIGURE WITH OVERRIDE;
GO

-- Set max server memory to XMB
EXEC sys.sp_configure 'max server memory (MB)', X;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Change max degree of parallelism to X (number of physical cores in a NUMA node)
EXEC sys.sp_configure 'max degree of parallelism', X;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Enable optimize for ad hoc workloads
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE WITH OVERRIDE;
GO


-- Enable remote admin connections
EXEC sys.sp_configure 'remote admin connections', 1;
RECONFIGURE WITH OVERRIDE;
GO

-----------------------------------------------------------------------------------------------

Enable-NestedVm & NestedVirtStatus

Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/Virtualization-Documentation/master/hyperv-tools/Nested/Enable-NestedVm.ps1 -OutFile C:\xfer\Enable-NestedVm.ps1

Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/Virtualization-Documentation/master/hyperv-tools/Nested/Get-NestedVirtStatus.ps1 -OutFile C:\xfer\Get-NestedVirtStatus.ps1

Set-VMProcessor -VMName "VMName" -ExposeVirtualizationExtensions $true



https://blogs.technet.microsoft.com/virtualization/2015/10/13/windows-insider-preview-nested-virtualization/

http://www.thomasmaurer.ch/2015/11/nested-virtualization-in-windows-server-2016-and-windows-10/

Differences in SQL Server on Linux

                                            Differences in SQL Server on Linux

Running SQL Server on Linux is an exciting new development that gives administrators an often-requested choice in the platform that they can use for deployments.
While the database engine is the same in Linux as it is in Windows, and most of the features that you know and love are supported across platforms, there are some minor differences and capabilities between the two platforms that you should be aware of.
First, let's talk about the installation requirements.
You need at least 3.25 GB of memory to run SQL Server on Linux, and it's only supported on the
Red Hat
SUSE
Ubuntu distributions at this time.

Unlike installations on Windows, you can only have a single instance installed in a Linux machine,
and the length of the host name where the SQL Server is installed needs to be 15 characters or less.

Finally, the file system of the disk needs to be the fourth extended file system, EXT4, with the exception of Red Hat, which also supports installations on an XFS formatted drive.
Remote database files can be stored on differently formatted drivers, but NFS, or network file system servers are not supported at this time.

Though Linux doesn't yet have a graphical user interface for SQL server instances, you can connect to an instance running on Linux from a Windows computer on the network.
When connecting to an instance of SQL Server on Linux from a Windows machine, you'll need to be running, at a minimum, version 17 of SQL Server Management Studio or SQL Server Data Tools for Visual Studio.

In Management Studio, keep in mind that at this time maintenance plans are not supported, Management Data Warehouse and the Data Collector are also not supported yet.

The use of Windows authentication or components that need to write to the Windows event log will not function properly, and the number of log files that are retained cannot be modified.

Further, SQL Server on Linux does not support distributed transactions requiring the Microsoft Distributed Transaction Coordinator service.

Finally, you won't be able to connect to SQL Server Configuration Manager to instances running on Linux.

So modifications to the server's configuration will need to be accomplished through the command line locally, or remotely through an SSH connection.

If you're migrating an existing database from a Windows SQL Server installation over to Linux, you'll make the job much easier if you upgrade the database to a SQL Server 2017 compatibility on Windows first,
then you can perform an backup and restore or a detach and attach procedure to complete the migration.
And when restoring a Windows database to a Linux instance for the first time, you'll need to include the "with move" clause in the transact SQL statement.

Finally, let's talk about some of the

 Unsupported features of SQL server on Linux.
includes

• Transactional replication.
•  Merge replication.
• Stretch DB.
• Polybase.
• Distributed queries with third-party connections.
• System extended stored procedures.
• Filetables
• CLR assemblies with external access or unsafe permissions set, and buffer pool extensions.
• Database mirroring.
• Agent alerts.
• Managed backups.

-->The SQL Server browser and the machine learning services that include support for the R and Python programming languages, are also not supported.

Now that might sound like a long list, but feature parity between platforms is a stated goal, and Microsoft is making huge strides every month to get there.

So if a feature that you require isn't yet supported, it's just a matter of time before it is.
Keep in mind that this is only the first time that we've had SQL Server on Linux, so the fact that so much is complete and ready to use, is quite an achievement.

                                                                                 ~ Concept taken  from linked.in\learning. 

                 Thanks for reading.