Top 10 New Features Of SQL Server

Silicon house provides the SQL server in the linux and windows platform. SQL is used for communicating with database. It is mainly used for the update the data and retrieve the data from the database

1. Intelligent Query Processing Enhancements
This is a set of enhancements that affect the behavior of the Query Optimizer, the component inside SQL Server that generates the execution plans for queries. This includes dynamic memory grants for rowstore tables, table variable deferred compilation, batch mode on rowstore and more.

2. Accelerated Database Recovery (ADR)
This is a completely new way for SQL Server to perform database recovery in the event of a transaction rolled back, an instance restart or an Availability Group failover. Instead of the sometimes unpredictable and less than desired time spent waiting for the database recovery to run, the SQL team has redeveloped how recovery works and has dramatically decreased how long this process takes.

3. AlwaysEncrypted With Secure Enclaves
This is the next version of AlwaysEncrypted, the encryption technology introduced in SQL Server 2016 that allows transparent column encryption without giving administrators access to the decryption keys,  the new Secure Enclaves technology, SQL Server can now securely encrypt a portion of memory to perform computations on these encrypted columns without the unencrypted values ever being exposed to the rest of the processes (or administrators)

4. Memory-Optimized Tempdb Metadata
 The SQL team has made optimizations to the tempdb code so that some of the metadata that can be a bottleneck on tempdb heavy systems can rely completely on memory and be optimized for RAM access.

5. Query Store Custom Capture Policies
Query Store is a great performance tuning and trending tool that allows for storing, measuring, and fixing plan regressions inside a SQL Server database. One downside of using it though is that sometimes it can store too much information, even for queries that the DBA might not be interested in or for queries that were part of a system utility or monitoring tool.

6. Verbose Truncation Warnings
Every single T-SQL developer knows the pain and grind of getting a truncation error. Some value somewhere does not fit under a new data type, but you don’t get any details at all. Then it’s a matter of trial and error until you finally figure out which value is the offending one

7. Resumable Index Build
QL Server now has the capability to stop an index rebuild operation in progress, keep the work that has been done so far and resume at some other point in time.

8. Data Virtualization With Polybase
Polybase is SQL Server’s module that allows fast and parallel T-SQL queries that can go out into external storage (usually HDFS on-prem) and bring the results seamlessly as a T-SQL result set. With SQL 2019, Polybase is getting expanded to support Oracle, Teradata, MongoDb and more.

9. Last Actual Execution Plan DMF
This is a new Dynamic Management Function called sys.dm_exec_query_plan_stats that will track the last ACTUAL execution plan for a query if you enable the lightweight query profiling feature (which you probably should do).

10. Multiple Internal Performance Improvements
 There are multiple internal performance improvements done by the SQL team for this release. There is a new index optimization for indexes that have a sequential key, temp table recompilation improvements, improved indirect checkpoint scalability, and more.

6 thoughts on “Top 10 New Features Of SQL Server

Comments are closed.