SQL Server 2016 - Query Store

Query Store in SQL Server:
Query store is a new component in SQL Server that captures Queries, Query Plans, Runtime Statistics, etc. inside the database.

It is available at database level and we can enforce the SQL Server query processor to execute the queries in a specific manner by using forcing plans.

Query store collects query texts and all relevant properties, as well as query plan choices and performance metrics.

It will provide the details regarding fire queries, query plans that it is used to execute the queries which will help in trouble shooting the performance issues.

Find below some of the things that we can do easily with Query Store:
1. Conduct system-wide or database-level analysis and troubleshooting
2. Access the full history of query execution
3. Quickly pinpoint the most expensive queries
4. Get all queries whose performance regressed over time
5. Easily force a better plan from history

Typical Performance trouble shooting that we can follow through Query store is:

We can easily enable Query Store by right clicking on the database and selecting the properties:

After you enable this option then we can see Query Store folder on expanding the database:

Click on any of the options available on expanding QueryStore, you will see the window as shown below:

Select the specific query, query plan and then select the option of ForcePlan OR UnForcePaln plan for that particular query.

SQL Server 2016 - Temporal Tables

Temporal Tables:

Temporal tables are nothing but maintaining historical information for the rows by the SQL server itself for the given table.

Instead of implementing SCD for the tables by the developers, if you create the table as Temporal Table then SQL Server automatically will take care of maitaining the history for the changing data.

To Create the temporal tables we should use below syntax (Here i have created Department table):

CREATE TABLE dbo.Department ( DepartmentID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, DepartmentName varchar(50) NOT NULL, ManagerID int NULL, ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT CAST('1900-01-01' AS DATETIME2) , ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31' AS DATETIME2) , PERIOD FOR SYSTEM_TIME ( StartDate, EndDate ) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) ); GO

1. Two additional Start & End date Audit columns of datetime2 datatype for capturing the validity of records. You can use any meaningful column name here, we will use StartDate and EndDate column names

2. Both the column names have to be specified in PERIOD FOR SYSTEM_TIME (StartDate, EndDate) clause along with the columns.

3. Specify WITH (SYSTEM_VERSIONING = ON) option at the end of the CREATE TABLE statement with optional (HISTORY_TABLE = <<History_Table_Name>>) option.

Once you create the table it will automatically create two tables internally

one for current data and other for the historical data.

When you are querying the table, you can mention the table name that you have mentioned in Create table statement that will automatically call historical table internally and will return the results.

If we want to see only historical records then we can query the historical table that we have mentioned in our create table script.

If there are no updates to the data then no rows will be available in the historical table.

Limitation of Temporal Tables:

1. We can't query Temporal tables using Linked Server

2. History table should not have constraints (PK, FK, Table or Column constraints).

3. INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.

4. TRUNCATE TABLE is not supported till SYSTEM_VERSIONING is ON and we can truncate if we Switch OFF this setting

5. Direct modification of the data in a history table is not permitted.

6. INSTEAD OF triggers are not permitted on either the tables.

7. Usage of Replication technologies is limited.

For more information refer Temporal tables Part 1 and Part 2

Enhancements to In-Memory OLTP in SQL 2016

In-Memory OLTP:

It is introduced in SQL Server 2014 and they enhanced many things in 2016 release.

Find below the list of enhancements that have been made in SQL Server 2016:

Feature/Limit	SQL Server 2014	SQL Server 2014
Maximum size of table	256 GB	2 TB
LOB (varbinary(max), [n]varchar(max))	Not Supported	Supported
Transparent Data Encryption (TDE)	Not Supported	Supported
ALTER PROCEDURE / sp_recompile	Not Supported	Supported
Nested natively compiled procedure	Not Supported	Supported
Natively-compiled scalar UDFs	Not Supported	Supported
ALTER TABLE	Not Supported	Supported (Only Offline)
Indexes on NULLable columns	Not Supported	Supported
Foreign Keys	Not Supported	Supported
Check/Unique Constraints	Not Supported	Supported
Parallelism	Not Supported	Supported
OUTER JOIN, OR, NOT, UNION, DISTINCT, EXISTS, IN	Not Supported	Supported

ALTER TABLE is an offline operation, by using this we can do following things:

a. Adding columns

b. Dropping columns

c. Indexes

d. Constraints

We can also change the bucket count values with REBUILD option but it needs 2x memory.

Stretch database feature in SQL Server 2016

Stretch Database:

It allows you to store a portion of a table in the Azure SQL Database.

Stretch Database feature offer local server performance for latest data and cloud storage for old data without any change to the application.

Users are mostly interested in latest data, with this feature latest data will be available in your local server where as the historical information will be moved to Azure.

When you enable stretch database, it creates a second database that is hosted in Azure. Then when you mark a table as “stretch”, SQL Server will automatically start moving its data into the cloud.

Currently only “archive table” mode is enabled, which assumes that it is working on a history table and moves all of the rows.

The “archive row” mode, which hasn’t been released yet, will use a WHERE clause to determine which rows to archive.

Common scenarios include rows that are more than a year old or have a flag indicating that the row is no longer live (e.g. completed orders).

No need to change anything in application : The SQL to query a stretch table is exactly the same as the SQL needed to query for a normal table. The query execution engine will automatically take care of distributing the query between the local and Azure-based server. This means you can enable stretch on a database without any changes to the applications using it.

Needs changes in backup and restore mechanism: Normal backups only include the locally hosted data. A full backup, including data located in the stretch database, will require a different procedure.

Limitations:

Below column types are not supported:

1. filestream
2. timestamp
3. sql_variant
4. XML
5. geometry
6. geography
7. hierarchyid
8. CLR user-defined types (UDTs)

Below features also not supported:

1. Column Set
2. Computed Columns
3. Check constraints
4. Foreign key constraints that reference the table
5. Default constraints
6. XML indexes
7. Full text indexes
8. Spatial indexes
9. Clustered columnstore indexes
10. Indexed views that reference the table
    For more information on how to enable the Stretch database feature refer SQL Server 2016 stretch database feature

New in SQL Server 2016 - SSIS

Below are the new capabilities introduced in SQL Server 2016 (Integration Services):

1. Always On support
2. Deploy Packages to Integration Services Server
3. Project Upgrade

Always On Support:

Always On Support is a disaster-recovery solution that provides an enterprise-level alternative to database mirroring.

In SQL Server 2016, SSIS introduces new capability in order to provide high-availability for the SSISDB database and its contents (projects, packages, execution logs, etc.).

You can add the SSISDB database to an AlwaysOn Availability Group. When a failover occurs, one of the secondary nodes automatically becomes the new primary node.

For more information please refer AlwaysOn for SSIS Catalog (SSISDB).

Incremental Package Deployment:

It will allows you to deploy one or more packages without deploying the whole project.

You can incrementally deploy packages using:

a. Deployment Wizard

b. SSMS (uses Deployment Wizard)

c. Stored procedures

d. Management Object Model (MOM) API

For more information please refer Deploy Packages to Integration Services Server.

Project Upgrade:

When you upgrade your SSIS projects from previous to the current version, the project-level connection managers will continue to work as usual and the package layout/annotations are retained.