What is new in SQL Server 2016 Database Engine?

In SQL Server 2016,

Configure multiple TempDB database files during Installation and set up.
https://hodentekmsss.blogspot.com/search?q=TempDB

The Query Store (new) stored texts, execution plans and performance metrics with the database. You have access to its dashboard related to query performance.

https://hodentekmsss.blogspot.com/search?q=query+store
[image]

Availability of Temporal Tables (history) which records all data changes.
https://hodentekmsss.blogspot.com/2016/07/temporal-tables-in-sql-server-2016-to.html

Built-in JSON Support(new). You can import/export, save and parse in JSON.
https://hodentekmsss.blogspot.com/2016/11/accessing-nested-json-formatted-text.html

Polybase(new) query engine integrated SQL Server with external data in Hadoop or Azure Blob storage. Import/export and executing queries all possible.
https://hodentekmsss.blogspot.com/search?q=polybase

Stretch Database(new) lets you dynamically, securely archive data from local SQL Server Database to an Azure cloud SQL database. querying is automatic both local and remote data by linked databases.
https://hodentekmsss.blogspot.com/2016/05/stretch-database-is-nice-feature-of-sql.html

In-memory OLTP:
Now supports FOREIGN KEY, UNIQUE and CHECK constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.
Supports tables up to 2TB (up from 256GB).
Has column store index enhancements for sorting and Always On Availability Group support.

New security features:
Always Encrypted: When enabled, only the application that has the encryption key can access the encrypted sensitive data in the SQL Server 2016 database. The key is never passed to SQL Server.
Dynamic Data Masking: If specified in the table definition, masked data is hidden from most users, and only users with UNMASK permission can see the complete data.

https://hodentekmsss.blogspot.com/2017/07/new-security-feature-in-sql-server-2016.html

Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.

How do you find Query Store related objects in SQL Server?

First off you should be using SQL Server 2016 because Query Store is a new feature in SQL Server 2016.

You should enable Query Store for the database you are going to use and you can enable Query Store using SQL Server Management Studio. You may review this post for enabling Query Store.

All objects in the SQL Server can be accessed using the sys.objects as shown:



SysObjcts_00

If you filter the Sys.Objects as shown here you can find Query Store related objects.

Select * from Sys.all_objects
Where name like 'query_store%' or name like 'sp_query%' or name like 'query_context%'

SysObjcts_02

The above query retrieves Views and extended stored procedures related to the Query Store.

What is a SQL Server Query Store and how do you enable it?

This is a new feature in SQL Server 2016 that provides with an insight into query plan choice and performance. As the query plan changes it helps you find the performance differences caused by the change.

Query store is automatic in that Query Store captures a history of queries, plans and runtime statistics for later review. It separates information chronologically by time windows so that you can view database usage patterns and monitor query plan changes that has taken place.

You need to enable the database to use this feature which an be done using SQL Server Management Studio as well as T-SQL Alter Database Set Option.

How do you turn it on using SQL Server Management Studio?

Since it is database related right click the database node after launching the SQL Server Management Studio and connecting to the server instance. The next image shows the result of right clicking the AdventureWorks 2014 database in SQL Server 2014.

QS_00.png

Click Properties to open the next window as shown.

QS_01

The Query Store Property has the following items:
General:
    Operation mode(actual)       default Off(disabled)
    Operation mode (Requested)   default Off but can be Read only or Read/write
Monitoring
    Data Flush Interval (Minutes)
    Statistics Collection Interval
Query Store Retention
    Max Size(MB)
    Query Store Capture Mode
    Size-based cleanup Mode
    Stale query Threshold(Days)

The previous window also shows the Current Disk Usage for database as well as Query Store.
As it is not enabled, the Query Store used is 0.0. You also have the option to purge the data.

You enable the Query Store feature by changing the Operation Mode(requested) from OFF to ON as shown.

QS_02

After it is enabled, a Query Store is added to the database as shown:

QS_03a.png