|SQL Server 2022 Revealed|
Page 2 of 3
Chapter 5: Built-In Query Intelligence Gets Even Better
This chapter is an extension of the previous one, detailing IQP features that require SQL Server 2022 (or more correctly, a dbcompat level of 160). The three features discussed all use information stored in Query Store, from previous query runs, to feedback into the query plan.
The first feature discussed is Parameter-Sensitive Plan (PSP) Optimization. Typically, when an execution plan is created, it’s based on the value of the parameters used when the query is first executed. If the parameters are atypical, this can lead to performance problems when the query is rerun with more typical parameter values. This problem is known as parameter sniffing. PSP Optimization allows multiple execution plans to exist, to cater for a range of parameter values. A detailed example of how it works and the problem it rectifies is given. This first release has some limitation (e.g. only supports ‘=’ in WHERE clause). PSP Optimization is a powerful new feature, where the optimizer can pick the best plan based on the runtime parameter values.
The next new feature examined is Cardinality Estimation (CE) Model Feedback. Cardinality is used to estimate the number of rows retrieved, and is of great importance in how an execution plan is created (e.g. if an index is used, and how it is used). The CE model changed in SQL Server 2014, generally giving better query performance, however, some queries ran more slowly – the author notes customers disabled the new CE model more than expected. Various methods exist to specify the CE model to be used (e.g. flags, hint). With CE information now stored in Query Store, it is hoped that most CE related problems are now fixed with CE feedback.
The chapter ends with a look at Degree of Parallelism (DOP) Feedback. DOP refers the number of threads a query can use, typically long running queries complete faster if they use multiple threads/CPUs. This feature aims to reduce DOP while achieving good enough performance. There’s a helpful diagram describing the DOP feedback mechanism, and a step-by-step example walkthrough.
This chapter provided a helpful look at some of the SQL Server 2022 features that automatically improve query performance. In each case, a useful step-by-step walkthrough is provided. PSP Optimization in particular seems to be very helpful, hopefully gone are the days when a production query fails because of slow performance, and a recompile is needed.
Chapter 6: The Meat and Potatoes of SQL Server
‘Meat and Potatoes’ in the title relates to core SQL Server features, which Microsoft always try to improve. These relate to security, scalability, performance, and availability.
SQL Server is the least vulnerable database, and security features continue to be added, including:
There are some useful new enhancements for Performance and Scalability, including:
For business continuity, availability is a priority. New/enhanced availability features include:
The chapter ends with a look at a miscellany of other engine improvements. These include: XML compression (giving significant space savings), auto-drop statistics when drop associated columns, and new wait types. I note there is a very useful XE, for query_abort giving details of the query e.g. timeout, cancelled, aborted.
This chapter contains a multitude of improvements, many of which occur without any great fanfare but could prove vital for certain businesses/workloads.
Chapter 7: Data Virtualization and Object Storage
Data Virtualization just means connecting to other sources of data, such that SQL Server can be viewed as a data hub, removing the need for complex ETL and ensuring data is always current. Recent versions of Polybase in SQL Server added improved features: Polybase in 2016 connected to Hadoop, 2019 added ODBC to Polybase to connect to Teradata, MongoDB etc. Since there were problems, another connection method was looked at: REST.
REST typically uses the internet to connect to service endpoints that support the HTTP protocol to get/update data. REST has the advantage that is it relatively lightweight and portable. It can be used to access Azure storage, and Amazon’s Simple Storage Service (S3).
The chapter shows how the latest version of Polybase can use connectors to various types of cloud storage (Azure Blob Storage, S3 etc). There’s a useful diagram and discussion on Data Virtualization on SQL Server 2022. Various file formats can now be accessed, including the popular parquet. There’s a helpful step-by-step walkthrough on using Polybase to access S3.
Since we now have new ways of accessing new types of data storage, the chapter looks at backup and restore with S3. One interesting revelation the author had was, taking a database backup from S3, and restoring it to SQL Server – and the example provided shows it worked. This suggests the boundaries/separations between vendors is (potentially) disappearing.
This chapter provided useful instruction in the continuing advancement of connecting disparate data sources. I enjoyed the authors revelation about restoring a database from S3 to SQL Server.
Chapter 8: New Application Scenarios with T-SQL
Various enhancements have been added to T-SQL, these include:
Many of these features would seem to be minor enhancements to the language, for which some developers would have created their own solutions, however these new additions should enhance future code.
Chapter 9: SQL Server 2022 on Linux, Containers, and Kubernetes
The chapter opens with a look at SQL Server 2022 on Linux, detailing the new features and highlighting a few that are not implemented (e.g. Microsoft Purview). New/enhanced features discussed include: cloud-connected features, IQP, Ledger for SQL Server, tempdb, Contained Availability Groups, REST-based Polybase, and various new T-SQL enhancements. Deployment of SQL Server 2022 is very similar to 2019, and brief details of this are provided. This is followed by details on connecting and using SQL Server 2022. Configuration using msssql-conf is outlined. A link is provided on how to configure Linux optimally, based on customer feedback.
Next, SQL Server 2022 Containers are examined, it’s noted that this is very similar to 2019. While VMs abstract SQL Server from the physical machine, containers abstract the application from the OS. In essence, containers enhance VMs rather than replace them. Details are provided on why and how to use containers, together with their many advantages (e.g. portability). Examples are provided.
The chapter ends with a look at SQL Server 2022 on Kubernetes (K8). Using containers opens up the world of K8, allowing containers to run at scale. There’s an interesting children’s video that explains what K8 is: https://youtu.be/4ht22ReBjno.
This chapter contains a mix of old and new. The overview of the installation of SQL Server on Linux hasn’t changed much since 2019, and containers don’t have many new features. The chapter seems to want to assure readers that the new SQL Server 2022 features that run on Windows, largely run on Linux too. There’s a useful reference to the author’s book on running SQL Server on Linux. For details see Kay Ewbank's review of Pro SQL Server On Linux
|Last Updated ( Tuesday, 04 April 2023 )|