What’s New in SQL Server 2019

SQL Server 2019 (15.x) builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud environments, and operating systems.

SQL Server 2019

SQL Server 2019 (15.x) introduces Big Data Clusters for SQL Server. It also provides additional capability and improvements for the SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.

Setup options 
New memory setup optionsSets the min server memory (MB) and max server memory (MB) server configurations during installation. See Database Engine Configuration – Memory page and the USESQLRECOMMENDEDMEMORYLIMITS, SQLMINMEMORY, and SQLMAXMEMORY parameters in Install SQL Server from the Command Prompt. The proposed value aligns with the memory configuration guidelines in Server Memory Configuration Options.
New parallelism setup optionsSets the max degree of parallelism server configuration during installation. See Database Engine Configuration – MaxDOP page and the SQLMAXDOP parameter in Install SQL Server from the Command Prompt. The default value aligns with the max degree of parallelism guidelines in Configure the max degree of parallelism Server Configuration Option.
Setup warning on Server/CAL license Product KeyIf an Enterprise Server/CAL license Product Key is entered, and the machine has more than 20 physical cores, or 40 logical cores when Hyper-Threading is enabled, a warning is shown during setup. Users can still acknowledge the limitation and continue setup, or enter a License Key that supports the operating system maximum number of processors.
WAIT_ON_SYNC_STATISTICS_REFRESHA new wait type in sys.dm_os_wait_stats dynamic management view. It shows the accumulated instance-level time spent on synchronous statistics refresh operations. See sys.dm_os_wait_stats.
Custom capture policy for Query StoreWhen this policy is enabled, additional Query Store configurations are available under a new Query Store Capture Policy setting, to fine-tune data collection in a specific server. 
LIGHTWEIGHT_QUERY_PROFILINGA new database scoped configuration.  The lightweight query profiling infrastructure (LWP) provides query performance data more efficiently than standard profiling mechanisms and is enabled by default.
sys.dm_exec_requests column commandShows SELECT (STATMAN) if a SELECT is waiting for a synchronous statistics update operation to finish before it continues the query execution. 
sys.dm_exec_query_plan_statsA new dynamic management function (DMF) that returns the equivalent of the last known actual execution plan for all queries.
LAST_QUERY_PLAN_STATSA new database-scoped configuration that enables sys.dm_exec_query_plan_stats. Allows you to enable or disable collection of the last query plan statistics (equivalent to an actual execution plan).
query_post_execution_plan_profileAn extended event that collects the equivalent of an actual execution plan that’s based on lightweight profiling, unlike query_post_execution_showplan, which uses standard profiling. 
sys.dm_db_page_info(database_id, file_id, page_id, mode)A new DMF that returns information about a page in a database. Returns information about a page in a database. The function returns one row that contains the header information from the page, including the object_id, index_id, and partition_id. 
Intelligent Query Processing 
Row mode memory grant feedbackExpands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators. This adjustment can automatically correct excessive grants, which result in wasted memory and reduced concurrency. It can also correct insufficient memory grants that cause expensive spills to disk.
Batch mode on rowstoreEnables batch mode execution without requiring columnstore indexes. Batch mode execution uses CPU more efficiently during analytical workloads but, until SQL Server 2019 (15.x), it was used only when a query included operations with columnstore indexes. 
Scalar UDF InliningAutomatically transforms scalar UDFs into relational expressions and embeds them in the calling SQL query. 
Table variable deferred compilationImproves plan quality and overall performance for queries that reference table variables. During optimization and initial compilation, this feature propagates cardinality estimates that are based on actual table variable row counts.
Approximate query processing with APPROX_COUNT_DISTINCTFor scenarios when absolute precision isn’t important but responsiveness is critical, APPROX_COUNT_DISTINCT aggregates across large datasets while using fewer resources than COUNT(DISTINCT()) for superior concurrency.
In-Memory Database 
Hybrid buffer poolNew feature of the SQL Server Database Engine where database pages sitting on database files placed on a persistent memory (PMEM) device will be directly accessed when required.
Memory-optimized TempDB metadataThis is part of the In-Memory Database feature family, memory-optimized TempDB metadata, which effectively removes this bottleneck and unlocks a new level of scalability for TempDB heavy workloads. 
In-Memory OLTP support for Database SnapshotsSQL Server 2019 (15.x) introduces support for creating Database Snapshots of databases that include memory-optimized filegroups.
Intelligent performance 
OPTIMIZE_FOR_SEQUENTIAL_KEYTurns on an optimization within the SQL Server Database Engine that helps improve throughput for high-concurrency inserts into the index. This option is intended for indexes that are prone to last-page insert contention, which is typically seen with indexes that have a sequential key, such as an identity column, sequence, or date/time column.
Forcing fast forward and static cursorsProvides Query Store plan forcing support for fast forward and static cursors.
Resource governanceThe configurable value for the REQUEST_MAX_MEMORY_GRANT_PERCENT option of CREATE WORKLOAD GROUP and ALTER WORKLOAD GROUP has been changed from an integer to a float data type, to allow more granular control of memory limits.
Reduced recompilations for workloadsImproves performance when using temporary tables across multiple scopes by reducing unnecessary recompilations.
Indirect checkpoint scalabilitySQL Server 2019 (15.x) introduces improved scalability for indirect checkpoint, which should help avoid these errors on databases that have a heavy UPDATE/INSERT workload.
Concurrent PFS updatesPage Free Space (PFS) pages are special pages within a database file that SQL Server uses to help locate free space when it allocates space for an object. Page latch contention on PFS pages is commonly associated with TempDB, but it can also occur on user databases when there are many concurrent object allocation threads. This improvement changes the way that concurrency is managed with PFS updates so that they can be updated under a shared latch, rather than an exclusive latch. 
Scheduler worker migrationWorker migration allows an idle scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage in situations where long-running tasks happen to be assigned to the same scheduler.
High availability 
Up to five synchronous replicasSQL Server 2019 (15.x) increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x). You can configure this group of five replicas to have automatic failover within the group. There is one primary replica, plus four synchronous secondary replicas.
Secondary-to-primary replica connection redirectionAllows client application connections to be directed to the primary replica regardless of the target server specified in the connection string. 
HADR BenefitsEvery Software Assurance customer of SQL Server will be able to use three enhanced benefits for any SQL Server release that is still supported by Microsoft. 
Accelerated database recoveryReduce the time to recover after a restart or a long-running transaction rollback with accelerated database recovery (ADR). 
Resumable operations 
Online clustered columnstore index build and rebuildThe ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.
Resumable online rowstore index buildThe ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.
Suspend and resume initial scan for Transparent Data Encryption (TDE) 
Data virtualization and SQL Server 2019 Big Data Clusters 
Scalable big data solutionDeploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. Read, write, and process big data from Transact-SQL or Spark. Easily combine and analyze high-value relational data with high-volume big data.  Query external data sources. Store big data in HDFS managed by SQL Server.  Query data from multiple external data sources through the cluster. Use the data for AI, machine learning, and other analysis tasks. Deploy and run applications in Big Data Clusters. The SQL Server master instance provides high availability and disaster recovery for all databases by using Always On availability group technology.
Data virtualization with PolyBaseQuery data from external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables, now with UTF-8 encoding support. 
Mission-critical security 
Always Encrypted with secure enclavesExpands upon Always Encrypted with in-place encryption and rich computations by enabling computations on plaintext data inside a server-side secure enclave. In-place encryption improves the performance and the reliability of cryptographic operations (encrypting columns, rotating columns, encryption keys, and so on), because it avoids moving data out of the database.
Certificate management in SQL Server Configuration ManagerCertificate management tasks such as viewing and deploying certificates is now possible by using SQL Server Configuration Manager. 
Data Discovery & ClassificationData Discovery & Classification provides capabilities for classifying and labeling columns in user tables. Classifying sensitive data (business, financial, healthcare, PII, etc.) can play a pivotal role in an organizational information protection stature.
SQL Server AuditAuditing has also been enhanced to include a new field data_sensitivity_information in the audit log record, which contains the sensitivity classifications (labels) of the actual data that was returned by the query.
SQL Server Machine Learning Services 
Partition-based modelingYou can process external scripts per partition of your data by using the new parameters added to sp_execute_external_script. This functionality supports training many small models (one model per partition of data) instead of one large model. See Create partition-based models.
Windows Server Failover ClusterYou can configure high availability for Machine Learning Services on a Windows Server Failover Cluster.
SQL Server Analysis Services 
Calculation groups in tabular modelsCalculation groups can significantly reduce the number of redundant measures by grouping common measure expressions as calculation items. 
Query interleavingQuery interleaving is a tabular mode system configuration that can improve user query response times in high-concurrency scenarios. 
Many-to-many relationships in tabular modelsAllows many-to-many relationships between tables where both columns are non-unique. 
Property settings for resource governanceThis release includes new memory settings: Memory\QueryMemoryLimit, DbpropMsmdRequestMemoryLimit, and OLAP\Query\RowsetSerializationLimit for resource governance. 
Governance setting for Power BI cache refreshesThis release introduces the ClientCacheRefreshPolicy property, which overrides caching dashboard tile data and report data for initial load of Live connect reports by the Power BI service. 
Online attachOnline attach can be used for synchronization of read-only replicas in on-premises query scale-out environments. 
SQL Server Integration Services 
Flexible file taskPerform file operations on Local File System, Azure Blob Storage, and Azure Data Lake Storage Gen2. 
Flexible file source and destinationRead and write data for Azure Blob Storage, and Azure Data Lake Storage Gen2. 
SQL Server Master Data Services 
Support for Azure SQL Managed Instance databasesHost Master Data Services on Azure SQL Managed Instance.
New HTML controlsHTML controls replace all former Silverlight components. Silverlight dependency removed.
Replication supportSQL Server 2017 (14.x) (CU18) and later support SQL Server Replication for instances of SQL Server on Linux.
Support for the Microsoft Distributed Transaction Coordinator (MSDTC)Distributed transactions are enabled on SQL Server on Linux by introducing MSDTC and RPC endpoint mapper functionality within SQL Server.
OpenLDAP support for third-party AD providersTo configure SQL Server on Linux to support Active Directory (AD) authentication, also known as integrated authentication.
Machine Learning Services on LinuxInstallation of SQL Server Machine Learning Services on Linux. Python and R scripts can be executed in-database using Machine Learning Services.
TempDB improvementsBy default, a new installation of SQL Server on Linux creates multiple TempDB data files, based on the number of logical cores (with up to eight data files). This doesn’t apply to in-place minor or major version upgrades. Each TempDB file is 8 MB with an auto growth of 64 MB. This behavior is similar to the default SQL Server installation on Windows.
PolyBase on LinuxPolyBase enables you to run external queries against remote data sources.
Change Data Capture (CDC) supportChange Data Capture (CDC) is now supported on Linux for SQL Server 2019 (15.x).
Microsoft Container RegistryThe Microsoft Container Registry now replaces Docker Hub for new official Microsoft container images, including SQL Server 2019 (15.x).
Non-root containersSQL Server 2019 (15.x) introduces the ability to create safer containers by starting the SQL Server process as a non-root user by default.
Red Hat certified container imagesStarting with SQL Server 2019 (15.x), you can run SQL Server containers on Red Hat Enterprise Linux.
PolyBase and Machine Learning supportSQL Server 2019 (15.x) introduces new ways to work with SQL Server Containers such as Machine Learning Services and PolyBase.
Developer experience 
Edge constraint cascade delete actionsYou can now define cascaded delete actions on an edge constraint in a graph database.
New graph function – SHORTEST_PATHYou can now use SHORTEST_PATH inside MATCH to find the shortest path between any two nodes in a graph or to perform arbitrary length traversals.
Partition tables and indexesGraph tables now support table and index partitioning.
Use derived table or view aliases in graph match querySpecifies a search condition for a graph. MATCH can be used only with graph node and edge tables, in the SELECT statement as part of WHERE clause.
Unicode support 
Support for UTF-8 character encodingSupports UTF-8 for import and export encoding, and as database-level or column-level collation for string data. Support includes PolyBase external tables, and Always Encrypted (when not used with Enclaves). 
New spatial reference identifiers (SRIDs)Australian GDA2020 provides a more robust and accurate datum that’s more closely aligned with global positioning systems. 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply