MongoDB Data Modeling
Master MongoDB data modeling with this comprehensive guide. Explore key steps, patterns, and best practices to optimize your database for performance and scalability.
Microsoft’s SQL Server is one of the best tools for organizations managing large databases. With the cloud becoming a strategic part of IT services, there is a continual release of new features and services for SQL Server available to us. SQL Server 2019, with in-cloud and on-premises versions, provides more storage and analytic tools.
SQL Server 2019 is available for both Linux and Windows versions with added support for Kubernetes and containers.With the support for big data built-in, SQL 2019 brings added security and compliance features, improved availability, performance, and advanced analytics to all your data workload.
GeoPits has compiled the list of the top 5 new features of SQL server version 2019 that will help you to get the best out of your SQL server 2019.
Intelligent Query Processing
Intelligent Query Processing in SQL server 2019 version improves the performance of existing workloads with minimal implementation effort. Built on intelligent query processing features of SQL 2017, the SQL 2019 version has its own set of capabilities and improvements that can meet all your data needs.
The new Intelligent Query Processing suite is developed to fix common query execution issues by adopting automatic corrective strategies at runtime. In addition, other areas in IQP:
- Provide batch mode on Rowstore without the need for column store indexes, improving the performance of analytical queries, while reducing the CPU utilization.
- Provide query processing with COUNT DISTINCT offering the benefit of high performance and a reduced memory footprint.
- Allocate additional batch and row mode memory grant feedback. If a query uses more than 50% of the memory allocated, the memory grant size will be reduced for consecutive executions.
- Improved table variable deferred compilation of plan quality and overall queries performance, using the actual cardinality of the table variable instead of a fixed guess.
Accelerated Database Recovery (ADR)
SQL Server 2019 ADR feature helps improve database availability, especially for long-running data transactions. With accelerated database recovery (ADR):
- Long-running transactions can roll back instantly, irrespective of the time that the transaction has been active or the number of updates that it has performed. ADR uses Persisted Version Store for tracking changes.
- Time taken by long-running transactions does not affect the recovery time, enabling faster database recovery irrespective of the size of active transactions in the system.
- The transaction log is shortened even when long-running transactions are active, keeping it in control.
Memory-Optimized Tempdb Metadata
Heavy workloads on Tempdb have always been a challenge. Microsoft has over the years made a lot of improvements to SQL Server to overcome this shortcoming. Tempdb system table metadata is one where too many sessions are trying to write to system tables.
With SQL Server 2019, you can now move over ten of the most-used Tempdb’s system tables into memory-optimized tables. It helps with resolving most of the issues in the query, thereby making TempDB more scalable.
Data virtualization with Polybase
With data growing exponentially, there are data sets hosted in siloed data sources across the organization. Data virtualization in SQL Server uses built-in technology known as PolyBase. You can use Big Data Clusters to bring all your data together when you query it, instead of physically moving or copying it in one place. The data stays in its original location, and you can query it just as if it was local.
You gain near real-time insights from all your data, enabling you to work with large data sets. You can query data from external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables now with UTF-8 encoding support.
Always Encrypted with Secure Enclaves
Microsoft released the Always Encrypted feature in SQL Server 2016 to secure data in the server database. It encrypts data on the client-side system before storing it in the SQL Server database. This feature had limitations, such as basic query operations to perform range scans on randomly encrypted columns.
SQL Server 2019 Always Encrypted with Secure Enclaves expands computational capabilities on encrypted columns in SQL Server memory.
The secure enclave offers secure-level isolation and memory encryption on every server. It is a protected region of memory within the Database Engine process that isolates application code and data from anyone with privileges, enabling the encryption of both storage and network data.
- The client driver forwards the column encryption keys to the secure enclave over a secure channel and submits the statement for execution.
- When processing the statement, the Database Engine assigns computations and keys on encrypted columns to the secure enclave.
- If required, the enclave decrypts the data and performs computations on the plaintext.
SQL Server 2019 release has a number of other interesting features.
These include Vulnerability Assessment, UTF-8 Support that improves data compatibility and performance, and Online Index CREATE Operations. Microsoft also announced an update for SQL Server 2019.
Accelerate Microsoft SQL Server 2019 Adoption with GeoPits
GeoPITS is a certified Microsoft Silver Partner with expertise in SQL Server database administration. We have the expertise to deliver complete SQL Server solutions, including migrating from older SQL server versions to newer ones. We offer Consulting, Support & Managed Services for MS SQL at the most affordable cost, tailor-made for your business.
The above features are new & it helps you to stay in the flow and connect and gain the desired information you want. Geopits has a comprehensive list of all the features in SQL Server 2019 that will help you to understand all the features at once.
Further reading
If you found this article helpful, you might want to check out these related resources,
