SQL Server 2019 and Beyond: New Features

Intelligent Query Processing

Intelligent Query Processing is a set of SQL Server features that provide improved query plan choice and better query performance. Here are some of the features that fall under Intelligent Query Processing:

1. Memory Grant Feedback

Memory Grant Feedback is a feature that monitors the actual memory required to complete a query. This feedback helps future queries to allocate memory resources efficiently, reducing memory pressure on the system. When a query completes, the system compares the initial memory grant with the actual memory required to execute the query. If the query used less than the estimated memory, the memory is released back to the system, reducing contention and wasted resources.

2. Scalar UDF Inlining

Scalar UDF Inlining is a feature that optimizes scalar UDFs (User-Defined Functions) automatically in the query plan. The performance gain depends on the complexity of the UDFs, but it can be significant. Instead of calling the UDF multiple times for each row, the query optimizer injects the UDF code into the query, reducing I/O operations and CPU time.

3. Table Variable Deferred Compilation

Table Variable Deferred Compilation is a feature that defers compilation of statements that reference table variables until the table variable cardinality is known. This allows SQL Server to generate a more optimized query plan, instead of relying on estimates. By delaying the compilation, the optimizer can estimate the cardinality more accurately, leading to better query performance.

4. Batch Mode on Rowstore

Batch Mode on Rowstore is a feature that enables batch-mode processing for queries that access rowstore tables. This feature parallelizes query processing, which can improve query performance for data warehousing queries. Batch mode processing works by processing several rows at once as a batch, rather than processing each row individually.

Intelligent Query Processing optimizes SQL Server queries and improves query performance for specific scenarios. Using these features can improve query performance in your applications, and it’s recommended to enable them whenever appropriate.

Accelerated Database Recovery

Accelerated Database Recovery is a feature in SQL Server 2019 that significantly reduces database recovery time and improves overall database availability. Here’s an overview of how it works:

When a SQL Server database is stopped abruptly or crashes, database recovery is needed to bring the database back to a consistent state. Traditional database recovery in SQL Server involves rolling back uncommitted transactions and then rolling forward all committed transactions. This process can be time-consuming, especially for larger databases, and can impact database availability during the recovery process.

With Accelerated Database Recovery, SQL Server saves a version store of the database each time a transaction starts. The version store acts as a journal of all changes made to the database, allowing for much faster recovery times. Here are some of the benefits of Accelerated Database Recovery:

Faster database recovery times: Recovery time is significantly reduced because SQL Server only needs to undo the changes made by the rolled-back transaction, instead of rolling forward all committed transactions.
Improved database availability: With faster recovery times, databases are available faster after a crash, reducing the risk of downtime.
Less transaction log space usage: The version store replaces the need for a lengthy transaction log, which can reduce the amount of disk space needed for transaction logs.

Under the hood, SQL Server 2019 accomplishes this by using a new recovery process that makes use of the version store. The new process has three phases:

Analysis: In this phase, SQL Server scans the transaction log and version store to identify the changes that need to be made to the database to bring it to a consistent state.
Redo: In this phase, SQL Server applies the changes identified in the analysis phase to the database.
Undo: If there are any rolled-back transactions, SQL Server uses the version store to undo those changes.

Overall, Accelerated Database Recovery is a major enhancement to database availability in SQL Server 2019. By reducing database recovery times and improving database availability, SQL Server becomes a more reliable database platform for critical applications.

Big Data Clusters

Big Data Clusters is a feature in SQL Server 2019 that allows you to deploy a scalable, secure, and performant data platform that combines SQL Server, Apache Spark, and Hadoop Distributed File System (HDFS) with Kubernetes containerization. Here’s an overview of the main benefits of using Big Data Clusters in SQL Server 2019:

Scalability: Big Data Clusters allows you to scale your data platform from terabytes to petabytes, by enabling seamless integration with Apache Spark and Hadoop Distributed File System (HDFS), allowing you to store and process large amounts of data.
Security: Big Data Clusters utilizes a multi-layered security architecture, including role based access control, network security, identity management, and data encryption, to ensure that your sensitive data is fully protected.
Performance: Big Data Clusters provides massively parallel processing (MPP) capabilities, which enables distributed data processing with Apache Spark, resulting in faster query processing.

With Big Data Clusters, you can easily deploy, manage and monitor containerized data workloads using Kubernetes. This provides many benefits, including portability, isolation, and faster deployment times. Here are some of the features of Big Data Clusters:

Kubernetes: Big Data Clusters is powered by Kubernetes, an open-source container orchestration system for automating deployment, scaling, and management of containerized applications.
PolyBase: PolyBase provides seamless connectivity between SQL Server and Big Data Clusters, enabling you to query Hadoop data using T-SQL, and also providing support for external tables and row-level security.
SQL Server Master Instance: The SQL Server master instance provides support for SQL Server workloads, including relational data, procedural programming, and business intelligence.

Overall, Big Data Clusters in SQL Server 2019 provides a highly scalable and secure data platform that is well suited for organizations that need to store and process large amounts of data. With the power of SQL Server, Apache Spark, Hadoop, and Kubernetes, Big Data Clusters provides a highly performant and flexible data processing platform that can be easily deployed and managed.

Enhanced Data Security

Enhanced Data Security is an important feature in SQL Server 2019 that provides a variety of tools and features to help protect sensitive data, including privacy, compliance and security. Some of the key features of Enhanced Data Security include:

1. Data Discovery and Classification

Data Discovery and Classification is a feature that helps identify sensitive data in your SQL Server databases. You can use built-in sensitivity labels that are based on data classification standards such as GDPR and PCI DSS, or you can create your own custom labels based on your organization’s needs. Using this feature, you can classify your sensitive data, and then create policies to regulate its use.

2. Vulnerability Assessment

Vulnerability Assessment is a feature that helps to identify security vulnerabilities in your databases. With this feature, SQL Server assesses your databases against a set of security best practices, using a set of rules customized for your environment. This will help you to identify potential vulnerabilities before they are exploited by attackers.

3. Advanced Threat Protection

Advanced Threat Protection (ATP) is a feature that provides real-time threat detection and response for your SQL Server environment. This feature continuously monitors your databases, and detects anomalies and potential security threats, such as SQL injection attacks and anomalous database activities. ATP can also alert you to activities that may indicate that users are attempting to exploit vulnerabilities in your system.

4. Transparent Data Encryption

Transparent Data Encryption (TDE) is a feature that helps to protect your data at rest by encrypting entire database files. This feature works at the file level, so it doesn’t require any application changes. With TDE enabled, your SQL Server database files are encrypted on disk, helping to protect your sensitive data in case of a breach.

Overall, Enhanced Data Security capabilities in SQL Server 2019 provides a range of tools and features to protect sensitive data in SQL Server databases, while still maintaining scalability and performance. By making use of these features, you can ensure that your SQL Server environment is protected from both external and internal threats.

Summary

SQL Server 2019 comes packed with new features and improvements, including Intelligent Query Processing, Accelerated Database Recovery, Big Data Clusters, and Enhanced Data Security. Developers now have access to better performance, scalability, and security for SQL Server workloads. By utilizing the powerful capabilities of SQL Server, businesses can process and analyze massive amounts of data quickly and securely. From my personal experience, I highly recommend utilizing the new features of SQL Server 2019 to improve performance and ensure the security of sensitive data.