SQL Server 2016 New Features

SQL Server 2016 New Features (welcome updates)

1. Always Encrypted
Always encrypted is designed to protect data while retrieving or when stored in
Databases, encryption/decryption keys reside in applications in customer trusted environment. SQL Server can perform operations on encrypted data. This help to hide data from DBA and SQL developer. Encryption/decryption of data happens transparently in application which helps for minimal code changes to existing application.

2. Dynamic Data Masking
This feature helps to mask/hide actual values of a sensitive columns and keeping rest as it. i.e. Email/Phone/Salary etc. this can be maintained at user/role level
This is also available in Azure SQL DB V12

3. Row level security
This help to maintain the policies which help to filter specific rows based on the user queries
This is also available in Azure SQL DB V12

4. Stretch Database
This feature helps to move cold or history data to move in azure environment at lower cost and hot and operational data in on-premises. Always encrypted feature is available in azure environment.

5. Real-time Operational Analytics
This feature combines in-memory OLTP with in-memory column store for real time analysis.
It helps to optimize system for optimal transactional performance and increased workload concurrency.

6. PolyBase into SQL Server
This feature help to integrate sql server with hadoop

7. Native JSON Support
8. Java script object notation (JSON) currently not supported natively by sql server to support it has to have t-SQL,SQLCLR, JavaSupport. Now in this release JSON Support feature help to integrate SQL server directly like xml.

9. Enhancements to AlwaysOn
This release will have up to three synchronous replicas and will support DTC and also support for round robin load balancing for secondary replicas. And will also have automatic fail over based on database health.

10. Enhanced In-Memory OLTP
This release will support more memory (in TB) and greater number of parallel CPU’s and will also support for extended t-SQL surface areas.

11. Revamped SQL Server Data Tools
This release will consolidate different version of SQL Server data tools into one.

SQL Server System Databases

MS SQL Server internally uses system databases to manage/maintain every SQL Server activity.
When we install SQL server following system databases created with default recovery model

1. Master(Simple): Records all system level info for an instance of SQL server, SQL server logins, link server
Configuration, service broker end points, system stored proc and functions
Full recovery model for master database is supported and can be changed if master database corrupted rest of the databases will also not work we need to run setup.exe to rebuild the instance of sql server.


2. Model(configurable: Is a template database, when we create new database this new database always contain the exact object/configuration of model database.


3. Msdb(Simple): Is used by SQL Server agent also by alert and jobs when we take backup(Log/diff/tran)
Respective entries also get stored in MSDB..BACKUPSET


4. Tempdb(Simple): Is a temporary database which generally hold temporary objects like (local/global temp table etc) and also intermediate result set. On start of SQL server instance tempdb database get re created & log space always reclaimed. We cannot backup tempdb database.


5. Distribution: When we configure any replication (i.e. Transactional, merge etc) a new Distribution database gets created having data file distribution.mdf and log file distribution_log.ldf


6. Resource: SQL server also uses read only Resource database to maintain all system level information under sys schema. These all system objects physically reside in resource database but logically appear in all databases under sys schema like sys.objects.

Resource database always has database id 32767 & uses mssqlsystemresource.ldf log and mssqlsystemresource.mdf as data file which always present at. :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\
SQL server cannot backup/restore detach/attach resource database like other databases, its .ldf & .mdf file should be copied and paste according to system requirement (copy paste is not possible with other databases).
Resource database info can be checked using below query.

SELECT
'Resource Database' AS 'Database Name'
, NAME AS [Database File]
, FILENAME AS [Database File Location]
FROM sys.sysaltfiles
WHERE DBID = 32767

SQL Server Copy-Only Backups

SQL Server support copy-only backups from SQL 2005 onwards, this is irrespective of traditional database sequences. When we take database backups it updates LSN (Log sequence number) in system table MSDB.dbo.BACKUPSET
When we take differential backup then it maintain differential_base_lsn which is after Database full backup without copy only, if we again take Database full backup then differential_base_lsn get changes.

With copy-only backup approach it won’t change differential_base_lsn remain same when we take next differential backup, so copy-only backup preserve existing log archive point.
Transaction log never truncated after copy-only backups, it is maintained by column is_copy_only Of system table MSDB.dbo.BACKUPSET.

Copy-only backup is possible using t-SQL code, power shell and SSMS.
A. t-SQL Code for full and transaction backup
Full Backup:
BACKUP DATABASE MyCopyOnlyDatabase TO disk='c:\copyonlydatabase.bak' WITH COPY_ONLY

Transaction Log Backup:
BACKUP LOG MyCopyOnlyDatabase TO disk='c:\Database\copyonlydatabasetran.trn' WITH COPY_ONLY

B. Using PowerShell
Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.

C. SSMS

Copy-only backup is not supported for differential backup if we take differential backup then above copy-only backup checkbox disables.

Transaction Log Physical Architecture

SQL Server uses transaction log for data integrity and data recovery. The sequences of log records are in set of physical files that implement transaction log.

SQL Server divide physical log files in small sized virtual log files, the size and number of virtual log files are decided by SQL server internally;

DBA can never configure virtual log files but can configure the size and number of physical log files.
When a database is created a physical log file is added, let us consider the example physical log file with six logical log files.

When new records are comes in, it added to the end of the logical log and expand toward the end of the physical log file.
Log truncation frees all the virtual log file prior to minimum LSN (minLSN), minimum LSN is the oldest number which require for successful database wide rollback.

When end of logical log reaches start of logical log, two things happens.
1. When physical file growth is enabled a new log record is added to extension
2. When physical file growth is disabled or disk has less space than error 9002 generates.
When database have multiple physical log file than logical file move through all physical files before wrap to first physical log file.

select * FROM sys.fn_dblog (NULL, NULL) // this gives us log file info.

Ghost Records in SQL Server

SQL Server support ghost records concept, ghost records are nothing but are deleted records from leaf node of Clustered/Non clustered index or versioned heap page of table which are just marked as deleted in page header logically but are physically available.
SQL Server supports this concept for faster delete/update operation to get optimum performance.
these logically deleted records (Ghost Records) are purged later using Ghost Records cleanup task which runs in every 5-10 seconds based on SQL Server versions.

We can forcefully clean/purge these ghost records using below DBCC command.
DBCC FORCEGHOSTCLEANUP;


To retain ghost records SQL Server have trace flag 661, this trace flag just disable system defined Ghost Records cleanup task which execute in ever 5-10 seconds.
DBCC TRACEON (661,-1) /* will disable Ghost records clean process */
DBCC TRACEOFF (661,-1) /*will enable Ghost records clean process */

We should always make sure this trace flag is used properly otherwise there will be quite possibility SQL server use a space which is not really required as these deleted records are physically available which are just marked as deleted logically or we can say GHOST RECORDS.

Examples:
/* DROP TABLE GhostRecords */
CREATE TABLE GhostRecords
(GhostId INT Identity(1,1),GhostName VARCHAR(100))

INSERT INTO GhostRecords(GhostName)
VALUES( 'GhostReocrd ' + CAST(ISNULL(IDENT_CURRENT('GhostRecords') ,1) AS VARCHAR))
GO 100

/*Records with Heap Table according to column Index_Type_Desc*/
SELECT 'QUERY1: Heap Table/without cluster Index' TableDetails , Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Create Clustered & Non Clustered Indexes*/
CREATE CLUSTERED INDEX ClsIdxGhostRecordsId ON GhostRecords(GhostId)
CREATE NONCLUSTERED INDEX NonClsIdxGhostRecordsName ON GhostRecords(GhostName)

/*Records with out Heap Table according to column Index_Type_Desc*/
SELECT 'QUERY2: Without Heap Table/Clustered Table' TableDetails , Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DBCC TRACEON(3604,-1)
DBCC IND(Ghostrecords,'GhostRecords',1)

DBCC PAGE(Ghostrecords,1,314,3) /* m_ghostRecCnt = 0 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /* m_ghostRecCnt = 0 This is IAM Page*/

/* Delete 10 Records from table */
DELETE FROM GhostRecords
WHERE GhostId<=10 /*Not >*/

SELECT 'QUERY3: 10 Records deleted, Ghost Cleanup Process Enabled' TableDetails , Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Disable Ghost Records Cleanup Task*/
DBCC TRACEON(661,-1)

DELETE FROM GhostRecords
WHERE GhostId<=20 /*Not >*/

SELECT 'QUERY4: 20 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DBCC PAGE(Ghostrecords,1,314,3) /*m_ghostRecCnt = 10 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /*m_ghostRecCnt = 0 This is IAM Page*/

/* Enable Ghost Records Cleanup Task */
DBCC TRACEOFF(661,-1)

DBCC PAGE(Ghostrecords,1,314,3) /*m_ghostRecCnt = 0 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /*m_ghostRecCnt = 0 This is IAM Page*/

SELECT 'QUERY5: Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DELETE FROM GhostRecords
WHERE GhostId<=30 /*Not >*/

SELECT 'QUERY6: 30 Records deleted, Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Disable Ghost Record Cleanup Task*/
DBCC TRACEON(661,-1)

DELETE FROM GhostRecords
WHERE GhostId<=40 /*Not >*/


SELECT 'QUERY7: 40 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DBCC PAGE(Ghostrecords,1,314,3) /*m_ghostRecCnt = 0 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /*m_ghostRecCnt = 0 This is IAM Page*/

/*Forcefully Start Ghost Cleanup Task*/
DBCC ForceGhostCleanup;

SELECT 'QUERY8: 40 Records deleted, Forcefully Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DELETE FROM GhostRecords
WHERE GhostId<=50 /*Not >*/

SELECT 'QUERY9: 50 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'


/* ALTER INDEX ALL ON GhostRecords REBUILD */
ALTER INDEX ALL ON GhostRecords REORGANIZE

SELECT 'QUERY10: 50 Records deleted, Index Reorganized/Rebuild, Ghost Record Removed' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DELETE FROM GhostRecords
WHERE GhostId<=60 /*Not >*/

SELECT 'QUERY11: 60 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Enable Ghost Cleanup Task*/

DBCC TRACEOFF(661,-1)

SELECT 'QUERY12: 60 Records deleted, Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

In Below Queries Result watch TableDetails & Ghost record Count