Sql Server 2016 New Features
quick link
home
top prev next
Query Store
Query Store: (query Optimizer )
Query Store configured at database level.
query store best practice
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
USE [QueryStoreDB];
GO
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;
sys.sp_xtp_control_query_exec_stats added to sys.query_store_planto
The Query Store feature maintains a history of query execution plans
with their performance data, and quickly identifies queries that
have gotten slower recently, allowing administrators or developers
to force the use of an older, better plan if needed.
Query Store manages its metadata in the local database, but it is disabled by default
-- a best practice is to enable it on the model database.
ALTER DATABASE MODEL SET QUERY_STORE=ON
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON
( OPERATION_MODE = READ_WRITE ,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 5 ) ,
DATA_FLUSH_INTERVAL_SECONDS = 2000 ,
MAX_STORAGE_SIZE_MB = 10 ,
INTERVAL_LENGTH_MINUTES = 10
);
Related views:
query_store_runtime_stats
query_store_runtime_stats_interval
query_store_plan
query_store_query
query_store_query_text
-- Finding the poorest performing queries over the last hour
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
-- how to unforce a plan ( against parameter sniffing )
unforce a plan by using either the Query Store interface in SSMS
or the sp_query_store_unforce_plan stored procedure.
top prev next
Polybase -- Direct Query To Hadoop
turn on TRACEFLAG: DBCC TRACEON(4631,-1);
Polybase ( Direct Query to Hadoop )
INSERT INTO EXTERNAL TABLE SELECT FROM TABLE
ALTER EXTERNAL DATA SOURCE
top prev next
JSON Support
The JSON type can be used for column and variable data types.
JSON Support (Java Script Object Notation (JSON)
SELECT * FROM Table FOR JSON AUTO;
SELECT * FROM Table FOR JSON AUTO, INCLUDE_NULL_VALUES;
SELECT * FROM Table FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;
SELECT * FROM Table FOR JSON PATH;
SELECT * FROM Table FOR JSON PATH, ROOT('info');
SELECT * FROM Table FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
Select IsJSON('{"name":"John","surname":"Doe","age":45}')
Select * from OpenJSON('{"name":"John","surname":"Doe","age":45}.)
ISJSON -Tests whether a string contains valid JSON.
JSON_VALUE .Extracts the value from JSON.
JSON_QUERY .Extracts subquery from JSON.
OPEN_JSON -Table value function that parses JSON text and returns rowsetview of JSON.
top prev next
Stretch Database into Azure
Stretch Database into Azure: ( Some data moved to Azure SQL Database in the cloud )
EXEC sp_configure 'remote data archive', '1';
GO
RECONFIGURE;
GO
USE [AdventureWorks2014];
GO
ALTER TABLE [StretchTest] ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON );
GO
-- backup to azure
EXEC sys.sp_reauthorize_remote_data_archive @azure_username, @azure_password;
GO
top prev next
In-Memory OLTP Enhancements
In-Memory OLTP Enhancements ( in-memory tables )
Supporting foreign keys check and unique constraints and parallelism.
-- Memory-optimized table
SQL Server drops memory-optimized tables whre the service is restarted, and all data is lost
top prev next
Enable Live Query Statistics For a Session
SET STATISTICS XML ON
top prev next
Columnstore Index
column store indexes, which are commonly used in data warehouse workloads
One clustered columnstore index, or one nonclustered columnstore index can be added to one table
top prev next
Temporal Database Support
Temporal Database Support
select * from department for system_time as of '2016.05.28';
select * from department for system_time all
select * from department for system_time contained in
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000 ORDER BY ValidFrom;
SYSTEM_VERSIONING ON|OFF
CREATE TABLE dbo.Employee
(
[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
, [Name] nvarchar(100) NOT NULL
, [Position] varchar(100) NOT NULL
, [Department] varchar(100) NOT NULL
, [Address] nvarchar(1024) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
CREATE TABLE dbo.TestTemporal
(ID int primary key
,A int
,B int
,C AS A * B
,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON);
ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE=dbo.TestTemporal_History,DATA_CONSISTENCY_CHECK=[ON/OFF])
);
top prev next
Columnstore (Index )
CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);
-- Regular Table, COLUMNSTORE Index
CREATE TABLE SimpleTable(
ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON SimpleTable;
GO
CREATE TABLE SimpleTable (
ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON SimpleTable
WITH (DROP_EXISTING = ON);
GO
top prev next
Always Encrypted ( Client Software )
To protect highly sensitive data by encrypting specific columns.
Randomized encryption: Credit Card Numbers, SOcial Security Numbers
Deterministic encryption: May need to index the column, like Birthday, Regions
Always Encryted stores encryption keys not on the server, but on the client side
Always Encrypted ( Client Software )
( client connection string: SqlCommandColumnEncryptionSetting: Disabled|Enabled|ResultSet )
column-level encryption
encryption at rest
encryption in transit
--> Create User ... ALLOW_ENCRYPTED_VALUE_MODIFICATIONS
eg1:
USE [Samples]
GO
CREATE COLUMN MASTER KEY MyKey
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/DE3A770F25EBD6071305B77FB198D1AE434E6014'
);
GO
eg2:
USE [Samples]
GO
CREATE COLUMN ENCRYPTION KEY [MyColumnKey] WITH VALUES
( COLUMN MASTER KEY DEFINITION = [MyKey], ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E008000630075007200720065006E00740075007300650072002F006D0079002F0064006500330061003700370030006600320035006500620064003600300037003100330030003500620037003700660062003100390038006400310061006500340033003400650036003000310034004D74119935C902E59F57A96C3E6F770826D247135FFFA759B5B013DF4DAF7CFB760A5864DD8381B91924D067BE4F574B50DE7F0D53F278E1C003B5D192865B808C1590224F4A4BB463255101C36D3089F46609B376D7B00FA9F9CEAF715398EECAB790AC6EC8BD18C17B3EB992CAE08FEA6A2F5A2BDDA4F5A700744E45861F993A3C488127E5897B30892DD2734DD5D84F096882A393D5877C5A20E392888FE0357F46DB578AEB4C677CFFCE2281276C4D12F3E5AC3BCCC09B78BB0E522D86F9B2CF989F14695B7CB95A478194ECBD175B5C7C1687B7589FD9145B2782CB0BBAB6F7F5B0AC7F8C256EB0D3D87ABAE4F73137FA4AFA387B791B54AC503B53271D );
GO
COumn Name: SSN, Column Master Ket Definition: MyKey
eg3:
USE [Samples]
GO
CREATE COLUMN ENCRYPTION KEY [MyColumnKey] WITH VALUES
( COLUMN MASTER KEY DEFINITION = [MyKey], ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E008000630075007200720065006E00740075007300650072002F006D0079002F0064006500330061003700370030006600320035006500620064003600300037003100330030003500620037003700660062003100390038006400310061006500340033003400650036003000310034004D74119935C902E59F57A96C3E6F770826D247135FFFA759B5B013DF4DAF7CFB760A5864DD8381B91924D067BE4F574B50DE7F0D53F278E1C003B5D192865B808C1590224F4A4BB463255101C36D3089F46609B376D7B00FA9F9CEAF715398EECAB790AC6EC8BD18C17B3EB992CAE08FEA6A2F5A2BDDA4F5A700744E45861F993A3C488127E5897B30892DD2734DD5D84F096882A393D5877C5A20E392888FE0357F46DB578AEB4C677CFFCE2281276C4D12F3E5AC3BCCC09B78BB0E522D86F9B2CF989F14695B7CB95A478194ECBD175B5C7C1687B7589FD9145B2782CB0BBAB6F7F5B0AC7F8C256EB0D3D87ABAE4F73137FA4AFA387B791B54AC503B53271D ); GO
CREATE TABLE [dbo].[Customers](
[CustomerId] [int] IDENTITY(1,1),
[TaxId] [varchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[Address1] [nvarchar](50) NULL,
[Address2] [nvarchar](50) NULL,
[Address3] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[PostalCode] [nvarchar](10) NULL,
[State] [char](2) NULL,
[BirthDate] [date]
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL
PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] );
GO
Row-Level Security
Row-Level Security ( RLS )
restricts which users can view what data in a table, based on a function
--> SESSION_CONTEXT, CONTEXT_INFO, reset by sp_reset_connection
--> sp_set_session_context @key=N'key', @value=N'value'[,@read_only={1,0}]
SESSION_CONTEXT ( N'key' )
--> SCHEMABINDING = OFF
-- old: CREATE FUNCTION dbo.fn_Orders(@SalesRep AS sysname)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS fn_Orders_result WHERE @SalesRep = USER_NAME();
GO
-- one username with different userid:
CREATE FUNCTION dbo.fn_Orders(@SalesRep AS int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS fn_Orders_result WHERE @SalesRep = CONVERT(SESSION_CONTEXT(N'UserId') AS int);
GO
CREATE PROCEDURE GetOrder
@OrderId int,
@UserId int
AS
EXEC sp_set_session_context @key=N'UserId', @value=@UserId;
SELECT * FROM Orders WHERE OrderId = @OrderId;
GO
Dynamic Data Masking
Four Types:
1): Email: partially obfuscate email addresses. This may be used to identify people, without revealing the full email address.
2): Phone number: partially obfuscate phone numbers. May be used to confirm which phone number is held in the database, without revealing the full phone number.
3): Partial: can be customized to specific requirements.
4): Random: can be customized to specific requirements.
Dynamic Data Masking
CREATE TABLE [dbo].[Customer](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[TaxId] [varchar](11) MASKED WITH (FUNCTION = 'default()'),
[FirstName] [nvarchar](50) MASKED WITH (FUNCTION = 'partial(3, "xyz", 1)') NULL,
[LastName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
( [CustomerId] ASC)
) ON [PRIMARY];
GO
ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] ADD MASKED WITH (FUNCTION = 'default()')
ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] DROP MASKED;
-- real data is still stored in the database, mask only when read out data
GRANT UNMASK TO MyUser;
REVOKE UNMASK TO MyUser;
top prev next
AlwaysON Enhancement
AlwaysON Enhancement:
Up to 3 Synchronous replicas for auto failover across domains
Round-robin load balancing of replicas
DTC & SSIS SUpport
Automatic failover based on database health
CREATE AVAILABLITY GROUP [2016DEMO] WITH DTC_SUPPORT=PER_DB
top prev next
Trace Flag
Allocations And Auto Growth ( Trace Flag )
TF1117 - Enabled By Default
TF1118 - Enabled By Default
Trace flag 1117
Trace flag (TF) 1117 is related strictly to file groups and how data files grow within them.
A file group is a logical container for one or more data files within a database.
TF 1117 forces all data files in the same file group to grow at the same rate,
which prevents one file from growing more than others, leading to the hotspot
issue described earlier in this chapter. Enabling this trace flag in earlier
versions of SQL Server is a minor tradeoff in performance.
Trace flag 1118
Administrators use trace flag 1118 to change page allocation from a GAM page.
When you enable TF 1118, SQL Server allocates eight pages,
or one extent, at a time to create a dedicated (or uniform) extent,
in contrast to the default behavior to allocate a single page from a mixed extent.
top prev next
Advanced Analytics - R:
Advanced Analytics - R:
USE [master]
GO
CREATE USER [] FOR LOGIN [] WITH DEFAULT_SCHEMA=[db_rrerole];
ALTER ROLE [db_rrerole] ADD MEMBER [];
GO
Exec sp_configure'external scripts enabled', 1;
Reconfigure;
exec sp_execute_external_script @language='R', @script=-- R code --
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'OutputDataSet<-InputDataSet'
, @input_data_1 = N'SELECT 1 as Col'
WITH RESULT SETS ((col intnot null));
EXEC sp_execute_external_script
@language = N'R' ,
@script = N' library(e1071);
irismodel<-naiveBayes(iris_data[,1:4], iris_data[,5]);
trained_model<-data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));',
@input_data_1 = N'select"Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" from iris_data' ,
@input_data_1_name = N'iris_data' ,
@output_data_1_name = N'trained_model' WITH RESULT SETS ((model varbinary(max)));
top prev next
Others
DBCC CheckTable, CheckDB, CheckFileGroup ... ... MAXDOP
Online Alter COlumn
EXECUTE AS CALLER
execute as user='MyUser'
Multiple TempDBs upon Install
TempDB: Multiple temp datafiles
SET SHOWPLAN_XML is set to ON