The SQL Server 2022

Workshop
Microsoft Azure Data

#sqlserver2022
#azuresql
Goals for this workshop

Learn in an interactive style what is new in SQL Server

2022

Evaluate why you should upgrade to SQL Server 2022

Learn how specific key features work in SQL Server

2022

Gain hands-on experience trying out new features

Walk away with resources you can use to learn more

The SQL Server 2022 workshop
Module 5
Module 1
Access new sources with data virtualization
Introduction to SQL Server 2022
and object storage

Module 2 Module 6

Connect SQL Server 2022 to Azure Enhance your application with new
T-SQL capabilities
Module 3
Accelerate performance with Module 7
built-in query intelligence Summary and closing

Module 4
Power your database with
security, scalability, and availability

aka.ms/sql2022workshop
aka.ms/sqlserver2022decks
aka.ms/sqlserver2022series
aka.ms/sqlserver2022demos
 Module 1

Introduction to SQL Server 2022

Overview of SQL Server 2022
Getting started with SQL Server 2022
Knowledge checks and summary
Introducing
SQL Server 2022
The next step for SQL Server

SQL Server 2016 SQL Server 2017 SQL Server 2019

Query Store SQL Server on Linux Data virtualization
Polybase Containers Intelligent query
processing
Always encrypted Adaptive query
processing Accelerated database
Row level security
recovery
Automatic Tuning
It just runs faster
Data classification
Graph database
Std Edition surface
area Machine learning services
Microsoft SQL – all edges and clouds
Intelligent Intelligent
edge cloud

Develop once deploy anywhere

Azure SQL Server SQL on Linux, Azure Arc- Azure Arc- SQL Server Azure SQL Azure SQL
SQL Edge 2022 containers, k8s enabled SQL enabled SQL on Virtual managed database
Server managed Machines instance
instance

Data
Language Tools Engine Machine Learning
Virtualization
 SQL Server 2022
Azure-enabled with continued performance and security innovation

Business continuity Seamless analytics Visibility over your Most secure over the Industry-leading
through Azure over on-prem entire data estate last 10 years1 performance and
operational data availability
+
70
Microsoft Purview 60

Critical vulnerabilities since 2013

50
40
Insights
30
20
Lineage 10
0 #1 OLTP performance2
#1 Non-Clustered DW

A
O L

P QL
gr 2
M er

IB cle
Q

st DB

AN
rv
yS

SA e S
Policies

ra
performance on 1TB3,

H
Se

Po M
L
SQ
3TB4, 10TB5, 30TB6, and
100TB7

Hero Link feature for

Azure Synapse Link
Ledger for SQL
Microsoft Purview integration Built-in query intelligence
capabilities Managed Instance Server

Most consistent data platform Edge to Cloud

Azure SQL Edge Azure Arc-enabled data Azure SQL

services
All TPC Claims as of 11/10/2022.
1
National Institute of Standards and Technology Comprehensive Vulnerability Database 2 http://www.tpc.org/4087; 3 http://www.tpc.org/3386; 4 http://www.tpc.org/3385; 5 https://tpc.org/3383; 6 http://www.tpc.org/3364; 7
https://tpc.org/3382
SQL Server 2022 capabilities
Cloud connected

Extending T-SQL Built-in query

intelligence

Data virtualization | Object Security, scalability,

storage availability

SQL Server
2022
Deploying SQL
Server 2022 Windows GUI or
command line

Supported OS
RHEL, Ubuntu,
Same upgrade SLES package managers
options as in
previous releases

dbcompat 160 Pre-installed container

SQL Server images
older dbcompat
Instance
supported

Side by side Azure marketplace images

for Virtual Machines
 What’s different about SQL Server 2022 setup?

Removed
R, Python, and Java packages1
Polybase Hadoop connectivity with
Java
Polybase scale out groups
Machine learning server
Distributed replay2
Deprecated features
Stretch database

Added
Azure extension for SQL Server
Pay As You go Licensing
1
Bring your own runtime packages 2
Will be available for separate
 Azure extension for SQL Server

Registers Azure Arc-enabled SQL Server

Capabilities for SQL Server 2022
AAD authentication
Microsoft Purview access policies
Microsoft Defender (supported by Monitoring Agent
extension)
Inventory, Pay as You Go Licensing, BPA, Automated
backups
Extension under the Azure Arc Agent framework
“Mimics” IaaS extension for Azure VM
Uses REST API to communicate with Azure
Supports proxy
Service on Windows and daemon process on Linux
Configure during SQL Server 2022 setup or after with
scripts
SQL Server Config Manager to stop or start extension
 https://aka.ms/sql2022editions
SQL Server 2022 editions
Azure-enabled with continued performance and security innovation

Enterprise
Mission-critical performance and
intelligence for tier 1 databases

Standard
Full featured database with for Feature highlights
mid-tier applications and data • Unlimited cores of CPU
marts
Express • Unlimited memory
Free, entry-level database
• Azure Synapse Link for SQL (multi-
for small web and mobile
Feature highlights threaded snapshot)
apps
• Up to 24 cores of CPU • Link feature for Azure SQL Managed
Feature highlights • Instance
Up to 128 GBs of memory
• Up to 4 cores of CPU • Built-in query intelligence: DOP feedback,
• Azure Synapse Link for SQL
CE Feedback, Memory Grant Feedback
• Up to 1410 MBs of memory • Link feature for Azure SQL Managed
• Contained Availability Group
• Microsoft Purview Policies Instance (basic availability groups)
• AVX 512 extension for batch mode
• Azure AD authentication • Buffer Pool Parallel Scan
• Built-in query intelligence: PSP Optimization, • Backups to S3-compatible object storage
Optimized plan forcing
+ Standard features
• Query store on by default for new databases
+ Express features
• Data Lake Virtualization
• Ledger + Express features

• Timeseries support

Developer
Free to use with all the features of Enterprise Edition specifically for dev/test in non-production environments

Build once and deploy across any SQL Server edition without changing
your app
 SQL Server 2022 on Linux, Containers,
Kubernetes
Linux distributions supported
RHEL 8.0 – 8.5
https://aka.mssqllinux
Ubuntu 20.04
https://aka.ms/sqlcontainers
SLELS 15
https://aka.ms/sqlk8s

New DTC transaction management support under WMI

Features not supported that are new to SQL Server 2022

TLS 1.3
Intel QAT backup compression

New MCR container images for SQL Server 2022 (2022-

latest)
 SQL Server 2022 on Azure Virtual Machine

Marketplace images
Windows and Linux
Dev, Web, Std, and EE Editions
https://aka.ms/sqlazurevm

Uses the IaaS Agent Extension

T-SQL snapshot backups supported with Azure disk

snapshots
Several VM sizes support AVX vector including AVX512
Portal experience always being updated
SQL Server 2022 features
AAD authentication
Microsoft Purview policies*
* Planned
 Knowledge check

Which of these is one of the major The Azure extension for SQL Server
categories for SQL Server 2022 provides
features? which of these capabilities?

A. ​Cloud connected A. ​Synapse Link

B. ​Azure Arc B. ​Managed disaster recovery

C. ​Contained Availability Groups C. ​AAD authentication

D. ​Built-in query intelligence D. ​Data virtualization

E. ​A and D E. ​Azure virtual machine

 SQL Server 2022 is Azure-enabled for disaster
recovery, analytics, and security

Built-in query intelligence reduces query tuning

and gains you performance with no code changes

Summary SQL Server 2022 has new innovations in security,

scalability, and availability

Data virtualization and object storage through

standard SQL interfaces

Extending the T-SQL language for new developer

scenarios
 Module 2

Connect SQL Server 2022 to Azure

SQL Server 2022 Hybrid
Managed disaster recovery with Azure SQL Managed Instance*
Near-real time analytics with Synapse link*
Azure Arc-enabled SQL Server*
Azure Security hybrid capabilities*
Knowledge checks and summary

Demos*
Has SQL Server been hybrid?

SQL Server on Azure Virtual Azure SQL Managed Instance Azure SQL Database
Machines Azure Storage

Replication
Linked Servers
Always-On BACKUP TO URL
Availability Groups
 SQL Server 2022 Cloud connected Azure Arc-
enabled
SQL Server

Azure
Microsoft
Active Directory
Purview
Azure Synapse Microsoft Defender
Analytics for SQL

Authentication Access Policies Azure Arc Services

Azure SQL
Managed Instance
Data changes Assessments
and Alerts

Replicated data Inventory, BPA,

and failover PAYG billing

Distributed Self-hosted Azure Arc Agents

Availability Groups integration and Azure
runtime extension for SQL
Server

SQL Server
2022
Business continuity through Azure SQL Managed
Instance
Disaster recovery in the cloud with link feature for Azure SQL Managed Instance
Challenge: Difficult to setup and
maintain a DR site Continuously replicate data to the cloud

Online
Use PaaS for managed disaster failover
recovery Offline
failback

Azure
Don’t have an AG? We will build DR in the
one for you with no replica or cloud
clustering required
SQL
Server
Built-in distributed availability 2022
Azure SQL
group (DAG) Managed
Instance

Offline Disaster Recovery using License free

backup/restore passive DR

Online Disaster Recovery in https:/aka.ms/

preview milink
SQL Server Distributed Availability Groups (DAG)
Distributed Availability Group
(DAG)

Datacenter Datacenter
1 Log changes 2

Primary Secondary Primary Secondary

Log changes
Log changes forwarded

Replica Replica Replica Replica

read/write Read Read

Availability scale Availability scale
Group 1 Group 2
 SQL Server 2016+ 
Migration
Link feature for Managed Instance SQL Server 2022  DR

Single step with SSMS

Distributed Availability Group (DAG) 3 using the replicate wizard
async

Primary
Create link
Database 4 Primary
seeding
Secondary
dbm
endpoint
1 dbm
endpoint
BC tier

Optional Replic Azure network Replic

secondary a a
Log changes

5
read only read/write read only
6
1 db per (AG) link
Availability Availability
2 Group 1
100 links for target MI
Group 2
Multiple instances across
versions
Online failover to Azure SQL Managed Instance
Single step with SSMS
using the failover wizard

1 Planned failover Forced failover

• Stop all writes to database • Remove link to MI
• Switch mode for MI from async to sync • Clean up DAG and AG when SQL Server is
• Switch DAG to sync available
• Verify LSNs match
• Remove link to MI
• Optionally clean up DAG and AG

2 Database on Azure SQL Managed Instance becomes read/write

3 Redirect application to connect to Azure SQL Managed Instance

4 Migrate instance level objects to Azure SQL Managed Instance

SQL Server database version compatibility
Azure SQL
SQL Server 2019 Managed Instance
database version !=
Version 15.X
dbcompat

Database 'WideWorldImporters' running the

upgrade step from version 904 to version
9XX
database database
version 904 version 957

The database was backed up on a server running version

Locked in with 16.00.0312. That version is incompatible with this
SQL Server 2019 RTM server, which is running version 15.00.2095. Either
restore the database on a server that supports the
Doesn’t change for CUs backup,or use a backup that is compatible with this
server.
Offline Disaster Recovery with SQL Server 2022

Azure SQL
SQL Server 2022 Managed Instance
Create new
Stop write
link to MI 5 1 workload
(Optional)

Restore backup COPY_ONLY backup

database database
version 957 version 957
3 2
Final version Migrate instance Aligned with
locked with objects SQL Server 2022
SQL Server 2022 RTM Redirect application Managed Instance still
4 updated with new
features that don’t affect
db version
Demo
Exercise 2.0
Link feature for Managed
Instance
Azure Synapse Link for SQL Server
Seamless analytics over on-prem operational data

Challenge: ETL expensive, out of

date,
and affects operational workloads
Break the wall between Store2
Power BI

operational and analytical stores

New change feed capability Cloud data

reduces impact on OLTP IoT data 12 123

SaaS data Azure Synapse Azure Machine
workloads Analytics Learning

Near real-time latency between

Azure Data Spark
SQL Server and Synapse ETL
SQL pools Store
Lake Storage Store
pools
On-premises
Analytics

Use SQL pools so harness the

full power of a scalable
warehouse solution https:/aka.ms/
synapselinksql
Analyze all your data using both
Using Synapse Link for SQL Server
Synapse Workspace

Dedicated SQL Pool

Operational
Databases

Landing Zone

Synapse Link
SQL Server 2022

for SQL

Azure
Self-hosted Integration Runtime
Azure SQL
Database

Operational
Databases
SQL Server 2022 database
Analytics
Synapse
Azure

Engine enhancements for change

feed

Similar for Azure SQL Database

Azure Synapse Link for SQL Server
How it works
Linked Service
for SQL

Self-hosted
Integration runtime 3 Synapse workspace
Control channel
Control
Plane Linked
Connection 1
Synapse link stored
procedures 5
Linked Service

database
For LZ
7
Table
snapshot
4
parquet &
schema
Ingestion
6 Log Landing Zone Service 2
changes

Change SQL dedicated

feed csv & pools
queue manifest ADLS Gen2
Demo
Exercise 2.1
Synapse link for SQL
Azure Arc-enabled SQL Server
Pay-as-you-go-licensing

Free inventory of instances and databases

SQL Best Practices Assessment

Azure Arc-enabled
SQL Server
Automated backups
Organize, inventory, and monitor
Governance and Security
Use with your existing SQL servers Automated security updates
SQL on Windows or Linux servers

Microsoft Defender for SQL

Azure Active Directory*

Microsoft Purview access policies*

* SQL Server 2022 only
Demo
Exercise 2.2
Azure Arc-enabled SQL
Server
Secure your SQL Server 2022 with Azure

Azure Active Directory Microsoft Microsoft Defender

Authentication Purview for SQL
Azure Active Directory for SQL Server https://aka.ms/aadsqlserver

Challenge: I need an alternative for

authentication than SQL and Windows domains

Azure Active Directory

SQL Server 2022

Azure VM

Secure auth without a

Azure extension domain
for SQL Server 1 3 AAD login
Azure RBAC

2 Multi-factor
authentication

Requires Azure
windows registry
extension
mssql.conf SQL Server 2022
Logins or users without
logins

Linked Servers
Demo
Exercise 2.3
Azure Active Directory
(AAD)
Microsoft Purview integration
Insights, lineage, and governance over your entire data estate

Challenge: I need central insights

and governance across all of SQL
Automatically scan on-prem SQL
Server to capture metadata Insights

Classify data using built-in and

custom classifiers and Microsoft Policies
Information Protection
sensitivity labels

Track lineage for usage

Lineage
and dependencies

Set up and control specific

access rights to SQL Server with
policies
Microsoft Purview access policies for SQL Server
Publish a policy from Purview
Works only with AAD accounts
No explicit login required
Engine understands policies to grant
Data* DevOps login and specific permissions
• Read • Performance Engine integrates policies with standard
• Modify Monitoring logins and permissions
• Security
Works across multiple SQL instances
Auditing
Remove policy removes authentication

Futures:
More granular scope
New policies
*Preview Self-service access https://aka.ms/
purviewsqlaccesspolicies
Microsoft Purview SQL Server Policy Architecture
Microsoft Purview
Azure
Active Directory

Data
2 DevOps
policies

Azure extension
for SQL Server 1 AAD login
3
4
windows registry
mssql.conf SQL Server 2022 Policy
cache
Demo
Exercises 2.4
Microsoft Purview Access
Policies
Microsoft Defender for Cloud—Databases
Protection
Protect SQLEnable
workloads through security posture ••management
Detect SQL security misconfigurations
and allow timely responses
SQL injection attacks
to threats
Remediate
• Brute-force attacks
1 2 • Unusual data exfiltration 3
Protected • Suspicious access or queries
SQL Workloads
Investigate in
Attacke
Azure
r SecOps
Sentinel
Vulnerabili
Security ty
Alert Assessmen
One-click t Sec admin
enablemen or DBA
t
Microsoft Automatic
Defender
for Cloud response

Cloud native Security Advanced Centralized

security posture management threat protection and integrated
1-click enablement of protect Discover, track, and remediate SQL Detect and response unusual Centralize security across all data
different type of SQL workloads workloads security and harmful attempts to breach assets managed by Azure and built-in
(IaaS or PaaS) misconfigurations SQL workloads integration with Sentinel and Purview
 Knowledge check

Link for Managed Instance feature is SQL Server sends changes to Synapse
supported on which SQL Server Link
versions? to what location?

A. ​SQL Server 2016 A. ​SQL Pools

B. ​SQL Server 2019 B. ​Landing Zone

C. ​SQL Server 2022 C. ​Azure Data Factory

D. ​B and C D. ​Linked Service

E. ​A, B, and C E. ​Linked Connection

 Knowledge check

Azure Arc-enabled SQL Server provides Microsoft Purview policies provide

what capabilities? access to SQL Server by:

A. Y
​ ou create a login and then map the login
A. ​Free inventory of instances and
to a Purview policy
databases
B. Y
​ ou create a policy in Purview based on
an Azure Active Directory (AAD) account
B. ​Managed Database Service
C. Y
​ ou create a role in SQL Server that maps
C. ​Synapse Link to a Purview policy

D. Y
​ ou create a AAD login and add it to a
D. ​Best Practices Assessment
policy role in SQL Server

E. ​A and D E. ​Purview scans SQL Server for logins

 Link SQL Server to Azure SQL Managed Instance
for managed disaster recovery

Link SQL Server to Synapse for automatic

near real-time analytics

Summary Azure Arc-enabled SQL Server provides hybrid

capabilities like inventory, PAYG, and BPA

Authenticate SQL Server logins and users with

Azure Active Directory (AAD)

Publish policies for data access without creating

logins
or roles with Microsoft Purview
 Module 3

Accelerate performance with

built-in query intelligence
Overview
The new Query Store
IQP NextGen foundations
IQP NextGen with dbcompat 140+
IQP NextGen with dbcompat 160
Exercises
Knowledge Checks and Summary
Query Store and Intelligent Query Processing
Accelerate query performance and tuning with no code changes

Challenge: Query tuning is

expensive and time consuming
Query Store on by default for
new databases
Intelligent Query Processing
(IQP)
Query Store support for read Next Generation
replicas from availability
groups*

Query Store hints to shape plans

with no code changes

New IQP scenarios enabled

through better together Query Store
capabilities

* Currently requires trace flag 12606

Query Store and Intelligent Query Processing
Accelerate query performance and tuning with no code changes

Challenge: Query tuning is

expensive and time consuming
Query Store on by default for Intelligent Query Processing
new databases (IQP)
Next Generation
Query Store support for read
replicas from availability
groups* Degree of Parallelism Cardinality Estimation Parameter Sensitive
(DOP) feedback (CE) feedback Plan (PSP) Optimization

Query Store hints to shape plans

with no code changes

New IQP scenarios enabled Hints

through better together
capabilities Feedback
store
Query Store
* Currently requires trace flag 12606
 Intelligent Query Processing (IQP) NextGen Query
Gain consistent performance with no code changes Store

Upgrade to SQL Server 2022 dbcompat 140+ dbcompat 160

Approximate Percentile Memory Grant Feedback Parameter Sensitive Plan (PSP)

Approximate versions for Percentiles Optimization
PERCENTILE_CONT and Smooth out oscillation with percentile- Cache multiple query plans for the same
PERCENTILE_DISC based calculation parameterized statement
Save the world from parameter sniffing?
Optimized Plan Forcing
Persist compile steps for forced plans
Memory Grant Feedback
Reduce compilation overhead
Persistence Cardinality Estimation (CE)
Persist feedback for cache eviction
feedback
Key principles Feedback loop for CE model choices
• “Do no harm” and query execution
• Options to disable Auto use query hints

Degree of Parallelism (DOP)

Adaptive Joins feedback
Interleaved Execution Feedback loop for DOP for a specific
Memory Grant Feedback query
Table Variable Deferred Compilation Adjust query DOP without recompilation
dbcompat 140 = IQP Gen1
Batch Mode on Rowstore
dbcompat 150 = IQP
Scalar UDF inlining
Gen1+2 Approximate Count Distinct
Optimizer fixes
Query Store enhancements

Query Store on by default for new databases

Query Store support for read replicas from

availability groups*

Query Store hints to shape plans with no code

changes

Query store is the perfect “store” for IQP

* Currently requires trace flag 12606
Query Store for read replicas*
querie
s

DBM
endpoint Query Store stats

DBM
endpoint

Primary Read
replica replica
Query Store Query Store
stats stats

Query Store queries Query Store queries

* Currently requires trace flag 12606

 Intelligent Query Processing (IQP) NextGen Query
Gain consistent performance with no code changes Store

Upgrade to SQL Server 2022

Approximate Percentile
Approximate versions for
PERCENTILE_CONT and
PERCENTILE_DISC

Optimized Plan Forcing

Persist compile steps for forced plans
Reduce compilation overhead
 Intelligent Query Processing (IQP) NextGen Query
Gain consistent performance with no code changes Store

Upgrade to SQL Server 2022 dbcompat 140+

Approximate Percentile Memory Grant Feedback

Approximate versions for Percentiles
PERCENTILE_CONT and Smooth out oscillation with percentile-
PERCENTILE_DISC based calculation
Optimized Plan Forcing
Persist compile steps for forced plans
Memory Grant Feedback
Reduce compilation overhead
Persistence
Persist feedback for cache eviction
What is a memory grant?
SELECT customer_id, customer_type,
customer_date
FROM customers
WHERE customer_date < GETDATE()
ORDER BY customer_date, customer_type

1,000,000, rows x
500 bytes per row
Memory Grant
Granted before execution = ~500MB
Allocation during execution
When can memory grants become a problem?

1,000,000, rows x
Memory Grant 500 bytes per row
Granted before execution
= ~500MB

RESOURCE_SEMAPHORE

Memory Grant Actual Memory Needed

1MB 500MB
tempdb

IO_COMPLETION
What is Memory Grant Feedback (MGF)?
Memory allocated at start of query execution
for operators like hashes and sorts

Memory Estimates could cause memory grant to be wrong

Grant Too small = tempdb spill

Too big = RESOURCE_SEMAPHORE waits

Workarounds include hints and resource governor

SQL Server First batch mode only and then row store

2017 and Observe actual grant after execution

2019
Adjust next execution if grant was incorrect
MGF Percentiles and Persistence
MGF Percentiles

Solves problem if memory grant feedback is constantly

moving the grant back and forth

We would disable before with volatility

Now use a trend over time (favor avoiding spills)

MGF Persistence

Store feedback in the Query Store to survive plan

cache eviction and restarts
 Intelligent Query Processing (IQP) NextGen Query
Gain consistent performance with no code changes Store

Upgrade to SQL Server 2022 dbcompat 140+ dbcompat 160

Approximate Percentile Memory Grant Feedback Parameter Sensitive Plan (PSP)

Approximate versions for Percentiles Optimization
PERCENTILE_CONT and Smooth out oscillation with percentile- Cache multiple query plans for the same
PERCENTILE_DISC based calculation parameterized statement
Save the world from parameter sniffing?
Optimized Plan Forcing
Persist compile steps for forced plans
Memory Grant Feedback
Reduce compilation overhead
Persistence Cardinality Estimation (CE)
Persist feedback for cache eviction
feedback
Feedback loop for CE model choices
and query execution
Auto use query hints

Degree of Parallelism (DOP)

feedback
Feedback loop for DOP for a specific
query
Adjust query DOP without recompilation
Parameter Sensitive Plan Optimization
Before With PSP optimization

exec exec
myproc 1 myproc 1

Scan
Seek Seek
Scan

exec exec
myproc 10 myproc 10
How does PSP optimization work?
Parameterized query

Dispatcher plan

Query variant
Query variant Query variant
‘Agents with > 10k to < 1M
‘Agents with < 10k listings’ ‘Agents with >= 1M listings’
listings’

Cached plan: seek + lookup Cached plan: scan + medium Cached plan: scan + large
+ small memory grant memory grant memory grant

• Not one plan per user

• Dispatcher plan and variant plans
• We have buckets to represent ranges of rows matching parameter
values
• Dependent on correct statistics
• Variants have different query text but same query hash
Is Parameter Sniffing gone?
 Only = operator supported
 Finite limit of variants
 Conservative ratio for min/max cardinality
 You could get a plan not optimal for all parameter values
within a variant
 Multiple parameters are supported
 New Query Store DMVs required to examine stored procs
 Issues using DMVs for plan cache
 Won’t use if you use conflicting hints, … (psp_skipped_reason_enum)
The problem of the Cardinality Estimation (CE)
model
What cardinality to use for query optimization is not always easy and obvious

CE Versions
• Legacy CE Model < dbcompat 120
• New CE Model >= dbcompat 120

Why the change?

• Assumptions from the original model we thought no longer correct and matched modern workloads
• Uh…this didn’t quite work out like we thought
• Customers upgraded to SQL Server 2014 and used dbcompat 120 and…problems.

Workarounds
• Don’t change dbcompat (ouch)
• Trace flags
• ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION
• Query hints
An example CE model problem

SELECT AddressLine1, City, PostalCode

FROM Person.Address
WHERE StateProvinceID = 79
AND City = 'Redmond';

Is there a correlation between Redmond and StateProvinceID 79?

1 Full independence 2 Partial correlation 3 Full correlation

Legacy model New model Either with a hint or TF
Hint or TF Hint or TF
Cardinality Estimation (CE) Feedback

Analyze = Optimizer
looks for repetitive
Validate = Check
query execution that Apply a query store Persist query hint in
performance has
match CE patterns hint the query store
improved
with incorrect
assumptions

We don’t enable if you

We follow the rules of
Query store hint takes have forced a plan,
Query Store hints so
over on subsequent use conflicting hints,
over time evaluate for
executions or are using the
best plan
legacy model
The pain of MAXDOP

More DOP = More

threads = More CPU
Setting MAXDOP
just right is a pain

Less DOP = Less

threads = Less
CPU

Is parallelism bad?
Parallel efficiency
The lowest DOP to achieve the same query
duration
DOP feedback architecture
SQL query

Query Store background

task
query stats
Eligible?

Query Processor
Provide feedback

DOP feedback Validate

DOP feedback
Stable, revert, min
Query Store
DOP feedback details
 Eligible queries must have a longer duration and repetitive

 It’s all about lowering overall CPU usage of the query

 Less CPU for DOP query = more CPU for everyone else

 Start from MAXDOP precedence setting and lower (never

higher)
 Factor out common waits = Focus on CPU

 Analyze again at recompile

“IQP is a symphony” – Kate Smith

PSPO creates a CE feedback creates a

variant plan query store hint

MGF feedback adjusts DOP feedback

the memory grant adjusts DOP
Exercises
Exercise 3.1
Parameter Sensitive Plan
(PSP) optimization

Bonus: Exercise 3.0

Persisted Memory Grant
Feedback

Advanced: Exercise 3.2

DOP feedback
 Knowledge check

Query Store is now on by default for

Parameter Sensitive Plan optimization
SQL Server 2022 under which
can help which scenario:
circumstances:

A. C
​ reate a new database with SSMS A. D
​ ata is skewed and you use ad-hoc
in SQL Server 2022 queries with your application

B. U
​ se CREATE DATABASE in SQL Server B. S
​ cenarios where more than one plan in
2022 cache would help with stored procedure
performance
C. R
​ estore a database backup
from SQL Server 2019 C. S
​ tored procedure performance using
parameters in filters with >= operators
D. ​A and B
D. ​All of the above
E. R
​ estore a version compatible backup
from Azure SQL Managed Instance
 Knowledge check

CE Feedback works by: Parallel efficiency is defined as:

A. ​Applies a query hint in plan cache​ A. ​ ynamically calculating the best setting for
D
sp_configure
B. ​ isables the legacy cardinality estimation
D
model B. ​ he lowest DOP setting for a query to achieve
T
similar duration across executions while
C. ​ riggers a recompile to see if the query gets a
T lowering CPU
different plan
C. ​ MAXDOP setting for the database that lowers
A
D. ​ ynamically change between legacy
D the overall CPU for the system
and new CE model
D. ​B and C
E. ​ ersist a query store hint for queries that
P
improve in performance
 The new Query store is on by default for new
databases, provides hints, works with replicas, and is
a store for new query intelligence

Upgrade to SQL Server 2022 with any dbcompat

and get approx. percentile and optimized plan forcing

Summary Upgrade and use dbcompat 140+ to enable

memory grant percentiles and feedback

Upgrade and use dbcompat 160 to light up

parameter sensitive plan optimization, ce feedback,
and dop feedback

Don’t forget all the great innovations from IQP Gen1

and 2 that come with dbcompat 140 and 150+
 Module 4

Power your database with security,

scalability, and availability
Overview
Security enhancements
Scalability enhancements
Availability enhancements
Purvi’s List
Exercises
Knowledge Checks and Summary
 Industry-proven database engine
Security, scalability, and availability

Secure Scalable Available

Ledger for SQL Server Buffer pool parallel scan Contained Availability Groups

Always encrypted enhancements “Hands-free” tempdb Cross-platform SNAPSHOT backups

Strict connected encryption (TDS 8.0) Auto async update stats concurrency Intel QuickAssistTM Technology
backup compression
Security enhancements

Ledger for SQL Server

Always encrypted enhancements

Strict connected encryption (TDS 8.0 and TLS

1.3)

PFX certificate support

New granular fixed server roles

Enhancements for Dynamic Data Masking

Ledger for SQL Server
Tamper-evidence track record of data over time

Challenge: I want the power of Bank Bank A

blockchain in a centralized Regulator

system like SQL Server

Use a cryptographically hashed

ledger detect tampering by malicious
actors Ledger
view

Built into SQL Server with T-SQL

Establish digital trust in a centralized Append-

Updatable History
only ledger
ledger table
system using blockchain technology. table

Attest to other parties that data Block N Block N-1 Block N-2
integrity has not been compromised
Trusted Database ledger
storage
Automatic digest storage
How to use Ledger for SQL Server

Save digest

Make changes Manual or auto

Create a Ledger INSERT, UPDATE/

table DELETE (only
updateable)
Updateable or
Append Only

View ledger history Verify Ledger

Ledger FAQ
How is this different from a temporal table?
• Built-in transaction auditing
• Append-only
• Database Ledger and digest for tamper evidence

How is this different than SQL Server Audit?

• Transaction history and audit built into database
• Digest verification

Can I ALTER a ledger table?

• You cannot ALTER a table to “turn off” ledger or disable system versioning
• You can alter the schema but there are some limitations

Can I drop a ledger table?

• Yes, but a history of the dropped table and ledger is kept
Ledger FAQ
How often do I need to save the digest?
• As frequent as you need to ensure the ledger is tamper proof

Does Ledger require more space?

• Updateable requires similar extra space as temporal
• Plus database ledger requires some minimal extra space for hashes and blockchain
• Digests small and separate but you may need a long history
• You can’t archive or truncate ledger tables and database ledger

Any perf impact?

• Append only should see minimal impact
• Updateable would have similar impact as temporal
Scalability enhancements

Buffer pool parallel scan

“Hands-free” tempdb

Auto async update stats concurrency

Auto drop statistics

XML compression

Advanced Vector Extension (AVX) 512 extension to

improve batchmode operations.

Improved columnstore segmentation

Ordered clustered columnstore index

 Tempdb lazy log
“Hands-free” tempdb flush
Tempdb != FUA

1 file = PFS, GAM, PFS concurrency SGAM and GAM

SGAM contention concurrency
Autogrow and
Add multiple files uniform default Latch contention
for tempdb gone
Trace flags 1117

SQL Server 2019

SQL Server 2022

Pre SQL Server 2019

and 1118 Now system table

pages become
SQL 2016 setup hotspot
auto adds
multiple files Tempdb
metadata
Trace flags not optimization ON
required by SQL
Server 2016 SGAM and GAM
contention
remain
Availability enhancements

Contained Availability Groups Parallel redo enhancements

Cross-platform SNAPSHOT DAG enhancements

backups
Resumable ADD CONSTRAINT
Intel QuickAssistTM backup
compression
CREATE INDEX concurrency

ADR enhancements
Last valid restore time in
backupset msdb table
In-memory OLTP memory
enhancements
LWW wins auto conflict peer-
to-peer replication
Shrink database concurrency
Contained Availability Groups
Synchronize instance level objects with data

Challenge: I have to manually

synchronize instance level objects with
Availability Groups
Create an Availability Group and
declare it be CONTAINED

We add a contained master and

msdb database to the AG

Connect with listener and

contained master and msdb are
used

Connect directly and default

master and model are used.

SQL Server agent enhanced to

understand contained msdb
How Contained AGs work

Primary Secondary

master msdb master msdb

Listener
or direct db
context
MyAG MyAG

<db> <db>

Log changes
MyAG_maste MyAG_maste
r r

MyAG_msdb MyAG_msdb
Demo
Exercise 4.2
Contained Availability
Groups
 https://aka.ms/sql2022mechanicsupdate

Cross-Platform Snapshot Backups

Before Now
VDI Program T-SQL suspend I/O
VSS and SQL Writer Perform your snapshot
T-SQL resume I/O

Why? Backup TB in seconds or milliseconds? Constant performance regardless of

size
Why would I halt I/O vs normal BACKUP? ANSWER: Restore faster!
Use storage that supports snapshots—Tip: Azure Storage does by default
Is this a SQL backup? ANSWER: Not a separate backup stream but you can restore
it
Tip: Use undocumented and unsupported
Works on Windows and Linux trace flag 3661 to test it
SQL Server 2022—Hardware acceleration &
offloading
Current customer demands
Stability for critical workloads
Consistent performance without oversizing
Remove scalability blockers
Make the most of current hardware

Intel QuickAssist Technology (QAT) Database Backup Improvements for SQL

Intel QuickAssist Technology (QAT) provides a Server 2022 with Intel QuickAssist
compression solution that integrates Technology (QAT):
hardware acceleration of compute-intensive
workloads on Intel platforms.

Intel QuickAssist Technology (QAT) with

SQL Server 2022 off-loads data compression,
enabling faster OLTP operations while
reducing the impact on the system’s
transaction throughput.
1
SOFTWARE BASELINE: Test by Intel as of 10/26/2021. 1-node, 2x Intel® Xeon® Platinum 8358 Processor on Wilson City (ref. platform), 32 cores with 1024 GB (16 slots/ 64GB/ 3200[3200]) total DDR4 memory, ucode 0xD000323, HT on, Turbo on, Windows Server 2019,10.0.17763.1999, [Hyper-V VM], 1x Intel
X550, SQL Server 2022 CTP1.1 private test build, 525GB Database, Database Backup with Compression​. WITH INTEL® QUICKASSIST ADAPTER 8970 TECHNOLOGY: Test by Intel as of 10/26/2021. 1-node, 2x Intel® Xeon® Platinum 8358 Processor on Wilson City (ref. platform), 32 cores with 1024 GB (16 slots/
64GB/ 3200[3200]) total DDR4 memory, ucode 0xD000323, HT on, Turbo on, with Intel® QuickAssist Adapter 8970, 1.70.16.4 driver version, Windows Server 2019,10.0.17763.1999, [Hyper-V VM], 1x Intel X550, SQL Server 2022 CTP1.1 private test build, 525GB Database, Database Backup with Compression​.
WITH INTEL®Intelligent Storage Acceleration Library: Test by Intel as of 10/26/2021. 1-node, 2x Intel® Xeon® Platinum 8358 Processor on Wilson City (ref. platform), 32 cores with 1024 GB (16 slots/ 64GB/ 3200[3200]) total DDR4 memory, ucode 0xD000323, HT on, Turbo on, with Intel® Intelligent Storage
Acceleration Library v2.30.0.0, Windows Server 2019,10.0.17763.1999, [Hyper-V VM], 1x Intel X550, SQL Server 2022 CTP1.1 private test build, 525GB Database, Database Backup with Compression​. Results may vary.
Intel QAT Details
How to use
• Install Intel drivers
• Hardware or software based
• Server configuration option
• BACKUP WITH COMPRESSION option

Preview observations
• Idle systems don’t benefit from QAT in hardware mode
• Hardware mode shines with multiple backup files and heavy CPU workloads
• QAT in software mode will usually outperform the default XPRESS
Purvi’s list

Reduce Buffer Pool I/O promotions

Enhanced core spinlock algorithms

Improved Virtual Log File (VLF) algorithms

Instant File Initialization (IFI) behavior for Log

file growth even with TDE enabled (does not
require special privilege)
Exercises

Exercise 4.0
Ledger for SQL Server

Bonus: Exercise 4.1

“Hands-free” tempdb
 Knowledge check

Independent verification for Ledger for SQL Server 2022 now eliminates what
SQL Server is done by: type of bottleneck for tempdb:

A. T
​ he database ledger through system A. ​PAGEIOLATCH
tables
B. ​PAGELATCH
B. ​The Ledger history view
C. ​Spinlocks
C. ​Database digests
D. ​A and B
D. ​Blockchain data in SQL Server tables
E. ​Row locks on system pages
 Knowledge check

What are benefits of using Intel

A Contained Availability Group replicates
QuickAssist
which objects:
with SQL Server 2022?

A. ​Linked Servers A. ​Improved backup compression

B. ​SQL Server Agent jobs B. C

​ ross-platform backups on SQL Server for
Linux
C. ​Logins
C. O
​ ffload CPU resources for SQL query
D. ​All of the above workloads

D. ​A and C

E. ​All of the above

 SQL Server 2022 is an industry proven engine with
enhancements in security, scalability, and
availability

Ledger for SQL Server provides a tamper evidence

record using blockchain technology

Summary Latch page concurrency improvements made

management of tempdb “hands-free”

Enhancements in availability include contained

AG,
cross-platform snapshot backups, and Intel
QuickAssist backup compression

There are over 25+ major engine enhancements

in SQL Server 2022
 Module 5

Access new sources with data

virtualization and object storage
Data Virtualization with Polybase
Data Virtualization with SQL Server 2022
Backup and Restore with S3 Object Storage
Exercises
Knowledge Checks and Summary
Remove data silos with Polybase—SQL Server
2019
Challenge: Integrating data from
numerous data silos is complex with
traditional ETL methods T-SQL

Solution
Access from multiple sources …
without moving data
ODBC NoSQL Relational databases Big Data
• Integrate data with data
virtualization

• T-SQL language with external tables

Azure SQL Database
• Distributed/scalable performance
with Polybase
Azure Cosmos DB

Excel
Data virtualization and object storage
Directly access any data on object storage

Challenge: I need to access data on modern object storage systems through SQL

SQL Engine

OPENROWSET, EXTERNAL TABLE, CETAS BACKUP/RESTORE

HTTPS REST API

ABS ADLS S3 compatible storage

Parquet, Delta, csv, text SQL backup files

Why S3, parquet, and delta?

S3 created by Parquet is an open- Delta lake is an

Amazon but…open source file format open-source
protocol storage architecture
Schema built-in with
Popular object delta tables
storage protocol Columnar format for
efficient Parquet files +
Inexpensive, compression and
durable, unlimited retrieval Basic transaction
log support
Data virtualization in SQL Server 2022
SQL
Engine

OPENROWSET, EXTERNAL TABLE, CETAS

HTTPS REST API

adls/abs S3 compatible storage

Polybase Services

adls abs

abfs wasb

Parquet, Delta, csv, text

Using REST API based data virtualization

SQL Database database

master
Engine key scoped
credential

Install system
polybase feature tables

external file external

Polybase format data source
instance configuration

User external
We support rowgroup tablesIngest, CETAS, join table
and column elimination OPENROWSET
for Parquet and Delta

abs, adls, S3 compatible storage

Parquet, Delta, csv, text

Archiving cold data to a lake - CETAS
Partitioned Table

Partition Partition 2 Partition

1 3
Parquet files

Partition Switch

SELECT *
CREATE EXTERNAL FROM
<External
TABLE AS SELECT (CETAS) Table>
Archive Table
External Table
Integrating SQL with Microsoft Fabric
Partitioned Table

Partition Partition 2 Partition

1 3

Partition Switch
Parquet files

SELECT *
CREATE EXTERNAL FROM
<External
TABLE AS SELECT (CETAS) Table>
Archive Table
External Table https://aka.ms/sqlfabricdemo
Using BACKUP/RESTORE with S3 compatible object
storage

BACKUP DATABASE <db>

TO URL = ‘s3:<bucket>/<file>’

RESTORE DATABASE <db>

FROM URL =
‘s3:<bucket>/<file>’

REST API

Can I migrate from

AWS RDS to SQL Server 2022?
Exercises
Exercise 5.0
Try out OPENROWSET
Exercise 5.1
Notebooks with parquet and S3
Exercise 5.2
Notebooks with delta and S3

Bonus: 5.3
Notebooks with backup/restore and
S3
Advanced: 5.4
Using minio server with S3
 Knowledge check

SQL Server 2022 can now access You can restore a database from AWS
external RDS
data sources through: to SQL Server 2022:

A. ​Using REST API to S3 object providers A. ​True

B. E
​ xport data to parquet files on REST B. ​False
API data sources from a SELECT

C. A
​ ccess parquet files on ODBC data
sources

D. ​A and B
 New REST API data sources include Azure
Blob Storage, Azure Data Lake Storage Gen2,
and S3 compatible object store providers

Summary SQL Server 2022 now natively recognizes

parquet and delta table file formats

SQL Server and backup and restore native

backups to and from S3 compatible object store
providers
 Module 6

Enhance your application with new

T-SQL capabilities
Overview
Exercises
Knowledge Checks and Summary
Language enhancements for developers
Use your T-SQL skills to do more than ever

Challenge: I want to get new features without learning a new language

ISJSON GREATEST DATE_BUCKET

JSON_PATH_EXISTS LEAST GENERATE_SERIES
JSON_OBJECT STRING_SPLIT FIRST_VALUE
JSON_ARRAY TRIM functions LAST_VALUE
DATETRUNC
IS [NOT] DISTINCT FROM
WINDOW clause
LEFT_SHIFT
RIGHT_SHIFT
BIT_COUNT
GET_BIT
SET_BIT
Exercises
You pick and choose
Exercise 6.0
JSON functions

Exercise 6.1
Core T-SQL

Exercise 6.2
Time Series

Exercise 6.3
Bit manipulation functions
 Knowledge check
Which important enhancement was
Which T-SQL function can produce JSON added to
formatted data? the STRING_SPLIT function in SQL Server
2022?

A. ​JSON_OBJECT A. ​separator

B. ​JSON_ARRAY B. ​multi-value expression

C. ​JSON_TEXT C. ​ordinal

D. ​A and B D. ​New string types

E. ​All of the above

 The most enhancements in T-SQL than many
previous releases

Summary Enhancements to process JSON

Core engine T-SQL enhancements for ANSI

compatibility and industry standards

New T-SQL functions to process time series data

 Module 7

Summary and closing

Summary of SQL Server 2022
Resources
Group Discussion and Final Q&A
 SQL Server 2022 is Azure-enabled for disaster
recovery, analytics, and security

Built-in query intelligence reduces query tuning

and gains you performance with no code changes

Summary SQL Server 2022 has new innovations in security,

scalability, and availability

Data virtualization and object storage through

standard SQL interfaces

Extending the T-SQL language for new developer

scenarios
Learn more
Learn more about SQL Server 2022 Try our demos
aka.ms/sqlserver2022 aka.ms/sqlserver2022demos

Download SQL Server 2022 Watch Microsoft Mechanics

aka.ms/getsqlserver2022 aka.ms/sqlserver2022mechanicsupdate

Get our decks Don’t miss us on Data Exposed

aka.ms/sqlserver2022decks aka.ms/sqlserver2022series

What’s new for SQL Server 2022 Read the SQL Server 2022 Blog Series
aka.ms/sqlserver2022docs aka.ms/sqlserver2022blogs

Read the book Take the Microsoft Learning Path

aka.ms/sql2022book aka.ms/learnsqlserver2022

Try out the workshop

aka.ms/sql2022workshop
Thank you!
Thank you!
Applications, Tools, and T-SQL

Powershell

sqlcmd

Azure Data Studio bcp

SQL Server
Management Studio
(SSMS)
Windows Only
New 19.X version

Tabular Data Stream (TDS) 8.0 and T-SQL

SSRS and SSAS in SQL Server 2022
SSAS
• Power BI support for SQL Server 2022 Analysis Services tabular models as a DirectQuery connection for composite
models
• Improved MDX query performance
• Improved resource governance
• Query interleaving now includes short query bias with fast cancellation

SSRS
• Enhanced Windows Narrator support for the new Windows OS (Operating Systems) and Windows Server
• Security enhancements
• Browser performance improvements with Angular
• Accessibility bug fixes
• Support for SQL Server 2022 instances report server catalog
• Reliability updates
What is a hybrid data platform?

A product or service offered both

on-premises and the cloud with
consistency

A product on-premises connected to

the cloud to enhance data
capabilities
Notes about Synapse Link
Catalog views, DMVs, and Extended Events for monitoring
and debugging

Datatypes not supported include image, text, xml,

timestamp, UDT, geometry, geography

Can’t be used with database that use these: temporal,

memory optimized, CDC, columnstore, graph

Use scheduled mode to spend less on ingestion

aka.ms/synapselinks
Create separate linked connections for same databases
to separate ingestion
ql
Only committed transactions sent to Landing Zone (LZ)

“Think” replication. Transaction commit not affected but

log can’t be truncated until committed in LZ

Landing Zone is cleaned up periodically for files no longer

needed
© Copyright Microsoft Corporation. All rights reserved.