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: 

• Ledger for SQL Server – blockchain technology for tamper-evident recording, useful for multi-party trust scenarios. There’s a useful description of how it works, with example walkthroughs, and a Q and A section

• Encryption Enhancements – Always Encrypted can now use multiple threads for better performance and supports new join types. Cryptography is now up to date with the latest standards e.g. use SHA-2 512 instead of less secure SHA-1

• Security Permission Enhancements – new roles give more granular fixed server-level roles. e.g. ##MS_ServerPerformanceStateReader##, is useful for temporary access for consultants, also retro added to SQL Server 2019 

There are some useful new enhancements for Performance and Scalability, including: 

• Columnstore and Batch Mode Improvements – Columnstore indexes now can be ordered, I guess this provides more granular segment boundaries, useful in segment elimination

• Scalability Improvements – better spinlock algorithm, better VLF algorithms. Can now apply Instant File Initialisation (IFI) to log files, this could be a big win for certain systems

• Hands-free tempdb – tempdb contention seems to have been a problem since forever. Various versions of SQL Server have tried to reduce the problems, in SQL Server 2022 various page latch problems have gone  

For business continuity, availability is a priority. New/enhanced availability features include: 

• Contained Availability Groups – Server level objects are maintained (e.g. logins, permissions, agent jobs). There are special versions of the master and msdb databases to allow it to be ‘server-less’. When a failover occurs the SQL Agent jobs are already present

• Backup/​Restore Enhancements – cross-platform snapshots, hardware offloading for compression, improved backup metadata

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: 

• JSON Functions – these include checking a JSON object type e.g. JSON array, and if a JSON path exists

• Time Series – these are particularly useful for IoT processes which make extensive use of date/time functionality. GENERATE_SERIES looks useful for quickly generating test data

• T-SQL miscellaneous – these include:

• DATETRUNC (truncates part of date e.g. sets hour to 00)

• WINDOW Clause (the OVER clause is very useful, the WINDOW clause allows you to specify the partitioning statement once and reuse many times in a SELECT)

• STRING_​SPLIT (can now get items in ordinal order)

• TRIM Function Extensions (can now trim characters from strings)  

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