Microsoft ne se repose jamais sur ses lauriers, et les nouvelles fonctionnalités continuent d’arriver, que ça soit en PaaS ou avec le produit On-Prem. Dans SQL 2022, Microsoft continue d’intégrer des nouvelles fonctionnalités d’intégration au cloud Azure, mais pas que… Nous allons faire une revue de ce à quoi s’attendre, et pourquoi c’est intéressant.

Cette liste n’est pas 100% exhaustive et se focalise sur les éléments essentiels.

Compatibilité avec le stockage S3 pour les backups et Polybase :

Le stockage S3 est principalement connu par les utilisateurs du cloud AWS. Cependant, cette technologie est utilisable autrement : dans des SAN/NAS compatibles, et par des composants logiciels OpenSource tels que Min.IO ( https://min.io/ ).

On peut donc désormais faire des BACKUP TO URL non plus seulement vers un storage account d’Azure, mais également vers un bucket S3 Amazon. Si on a un stockage S3 customisé, on peut également s’en servir.

Mais ce n’est pas tout : le stockage S3 peut aussi être utilisé pour Polybase et ses capacités de Big Data. En plus de la capacité de connexion à un stockage S3, le support des fichiers parquets (ces fichiers de stockage vertical de données, compressés et structurés : https://fr.wikipedia.org/wiki/Apache_Parquet ).

Un article viendra prochainement expliquer comment interroger ces données avec Polybase et un S3 customisé.

Groupe de disponibilité distribué avec le PaaS Managed Instance dans Azure :

Les groupes de disponibilités sont traditionnellement utilisés pour de la haute-disponibilité sur des configurations On-Prem. Mais certains outils analytiques sont présents dans Azure et pourraient vous intéresser, sans pour autant vouloir déporter toute votre configuration dans le cloud.

Il est donc désormais possible de monter une Managed Instance dans Azure (PaaS, donc), et l’intégrer comme un réplica de votre configuration AlwaysOn. Vous pourrez l’utiliser en lecture seule pour les outils analytiques tels qu’Azure Synapse.

Améliorations des groupes de disponibilité AlwaysOn :

Groupe de disponibilité autonome (contained Availability Groups)

Selon moi l’une des nouveautés les plus sympa ! Attention, rien à voir les technologie Docker & Cie… : il s’agit de la capacité à conteneuriser à l’intérieur d’une base dans un groupe de disponibilité certains objets qui traditionnellement sont au niveau de l’instance : les logins et les jobs en particulier ! Ainsi, il n’y aura plus à déployer sur tous les réplicas vos jobs applicatifs en ajoutant une logique de détection du rôle du réplica (principal ou secondaire). Les déploiements sont simplifiés, leur exécution également.

Connexions TCP multiples

La synchronisation entre les réplicas pourra se faire dans plus d’une connexion, pour paralléliser les échanges. Cela est utile quand les réplicas sont sur des liaisons réseaux distantes (par exemple un réplica Managed Instance dans Azure), en augmentant le débit malgré une latence réseau élevée.

Parallel redo

Pour fonctionner avec ces connexions TCP multiples, le Redo Thread a été optimisé pour fonctionner en parallèle, par base, tout cela derrière un seul latch pour augmenter la vitesse d’application. Cela fonctionne dans le cadre de la synchro AlwaysOn, mais également pour la récupération en cas de crash.

Améliorations du Query Store :

Disponibilité du QS sur les réplicas secondaires dans une configuration AlwaysOn :

Dans le cadre d’un AlwaysOn où le réplica secondaire est utilisé pour de la lecture seule, les requêtes exécutées sur celui-ci seront enregistrées dans le Query Store en étant transmises au réplica principal. Enfin des possibilités d’analyse de performances sur les réplicas dédiés à du reporting !

Query Store Hints

Dans le cas où l’on aperçoit des requêtes dans le Query Store qui nécessiterait une optimisation passant par un hint ( OPTION(…) à la fin de la requête), mais où la modification du code n’est pas aisée (parce que c’est un logiciel « éditeur » et que les modifications du code ne sont pas acceptées/possibles, parce que le processus de validation des changements du code applicatif sont longs etc…), il est désormais possible d’attraper un plan d’exécution et lui forcer un hint.

On peut ainsi forcer des actions du type :

Recompilation à chaque exécution du code
Limitation du memory grant
Limitation du degré de parallélisme
Désactivation du « Row Goal » dans les requêtes « TOP »
Forçage d’un type de jointure (hash au lieu de loop par exemple)
Forcer un niveau de compatibilité (110 au lieu de 160 par exemple).
Forcer l’option « legacy cardinality estimator »
Forcer la cardinalité moyenne sur toutes les valeurs d’un prédicat

Memory Grants Feedback basé sur le QS

Il s’agit d’une amélioration es MGF qu’on trouvait déjà en SQL 2019, mais qui seront désormais basés sur les données cumulées sur une durée longue avec le Query Store, plutôt que juste le cache des plans d’exécution.

Il est désormais aussi possible de fixer de manière persistante un memory grant.

Un nouvel algorithme d’estimation du memory grant basé non plus juste sur la valeur du dernier plan, mais sur un calcul basé sur les variations des memory grants précédents.

DOP Feedback

SQL Server propose par l’option DOP_FEEDBACK d’ajuster automatiquement le niveau de parallélisme de certaines requêtes se répétant souvent pour trouver les performances les plus rapides.

Cardinality Estimator Feedback

Enorme promesse de SQL Server : la capacité à identifier quand il y a un écart significatif d’estimation de cardinalité, et de modifier les plans d’exécution à la volée.

Optimized Plan Forcing

Dans le cas d’un usage de plans forcés par le Query Store, un nouveau mécanisme de « pre-caching » est en place afin de gagner du temps d’exécution.

Ledger

Un peu la réponse de Microsoft aux problématiques qu’essaie de résoudre la Blockchain : les données sont considérées comme inaltérables, car les lignes sont ajoutées en se basant sur une valeur cryptographique des précédentes lignes.

Se veut moins gourmande en énergie que la plupart des blockchains, mais n’est pas décentralisée comme peuvent l’être traditionnellement les blockchains.

Support d’Azure Active Directory

On peut désormais simplifier l’authentification si l’on a un tenant Azure Active Directory en s’en servant comme référentiel. Plus besoin de contrôleur de domaine !

Always Encrypted avec “secure enclaves”

Certaines opérations SQL étaient impossible à réaliser de manière complètement sécurisé avec AlwaysEncrypted (telles que des jointures ou des agrégations). Désormais, avec les « secures enclaves » si le CPU supporte Intel SGX, il est possible de réaliser ces opérations dans un contexte complètement sécurisé ET multi-thread.

Nouveaux rôles serveurs

Les rôles serveurs suivants ont été ajoutés :

##MS_DatabaseConnector## : Peut se connecter avec un login mais pas de user.

##MS_DatabaseManager## : Peut créer et droper des bases de données. L’utilisateur qui a créé sa base en devient le propriétaire et peut se connecter sans login en tant que dbo.

##MS_DefinitionReader## : Peut lire toutes les vues des catalogues.

##MS_LoginManager## : Peut créer et supprimer des logins.

##MS_SecurityDefinitionReader## : peut accéder aux vues des catalogues, mais uniquement celles couvertes par VIEW ANY SECURITY DEFINITION.

##MS_ServerStateReader## : un rôle qui remplace le traditionnel GRANT VIEW SERVER STATE

##MS_ServerStateManager## : Le même que ServerStateReader mais dispose en plus du ALTER SERVER STATE.

Ordered clustered columnstore index

Possibilité désormais de définir un ordre de tri pour les Cluster ColumnStore Index, améliorant du coup l’efficacité de la compression et donc des performances en lecture.

Compression XML

Capacité à compresser les données et les index sur des colonnes de types XML.

Support AVX-512

Pour les plans d’exécution en mode « batch », les CPU disposant du jeu d’instruction AVX-512 voient leur performance améliorées.

Accès concurrents GAM/SGAM pour les bases utilisateurs en plus de la TempDb

Après avoir amélioré les mécanismes de verrouillages GAM/SGAM de la TempDb en SQL 2019, SQL 2022 propose d’améliorer encore ces allégements de latches sur les bases utilisateurs.

Buffer pool parallel scan

Amélioration des performances dans le cas de requêtes avec de gros scans dans le buffer en autorisant la parallélisation.

Gestion manuelle des snapshots pour les backups sans client VDI.

Si le stockage est compatible, il est possible de séquencer des opérations de backups SQL (BACKUP DATABASE / BACKUP LOG) avec des freezes de stockages pour faire des sauvegardes extrêmement rapides. Cela nécessite des actions croisées SQL / OS-Stockage, mais permet de se passer d’un logiciel de backup tiers.

Ajout de fonctions

DATE_BUCKET()

Permet de trouver le premier élément d’une série de date. Par exemple:

SELECT DATE_BUCKET(WEEK, 1, getdate());

Cela retournera la date du premier jour de la semaine courante.

GENERATE_SERIES() :

Permet de générer une série de valeur. Par exemple :

SELECT value FROM GENERATE_SERIES(START = 1, STOP = 10, STEP = 1);

Cela va retourner une série de 10 valeur incrémentée de 1 à chaque fois.

FIRST_VALUE() / LAST_VALUE()

Permet de retrouver la première ou la dernière valeur d’un agrégat. Par exemple avec la base d’exemple Microsoft :

USE AdventureWorks2012;

GO

SELECT Name, ListPrice, FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive

FROM Production.Product

WHERE ProductSubcategoryID = 37;

Cela renvoie :

Name	ListPrice	LeastExpensive
Patch Kit/8 Patches	2.29	Patch Kit/8 Patches
Road Tire Tube	3.99	Patch Kit/8 Patches
Touring Tire Tube	4.99	Patch Kit/8 Patches
Mountain Tire Tube	4.99	Patch Kit/8 Patches
LL Road Tire	21.49	Patch Kit/8 Patches
ML Road Tire	24.99	Patch Kit/8 Patches
LL Mountain Tire	24.99	Patch Kit/8 Patches
Touring Tire	28.99	Patch Kit/8 Patches
ML Mountain Tire	29.99	Patch Kit/8 Patches
HL Road Tire	32.60	Patch Kit/8 Patches
HL Mountain Tire	35.00	Patch Kit/8 Patches
HL Road Tire	32.60	Patch Kit/8 Patches
HL Mountain Tire	35.00	Patch Kit/8 Patches

La fonction LAST_VALUE() fait l’inverse et renverrai donc l’élément le plus cher des Product (HL Mountain Tire).

GREATEST() / LEAST() :

Fait un peu la même chose que FIRST_VALUE() / LEAST_VALUE(), mais avec une série d’expressions plutôt que le résultat d’un agrégat.

Par exemple :

 select GREATEST(1,3,11)

renvoie 11.

Avec des chaines de caractères, ce sera l’ordre alphabétique qui sera utilisé :

select GREATEST('truc', 'machin', 'chose')

renvoie « truc ».

Bien entendu, LEAST() fait l’inverse.

Nous aurons l’occasion de détailler prochainement certaines fonctionnalités plus en détail dans des articles dédiés.

Vous pouvez commencer à expérimenter la prochaine version de SQL Server ici : https://info.microsoft.com/ww-landing-sql-server-2022.html

Continuez votre lecture sur le blog :

Query Store : bloquer les régressions de plans d’exécution et Capture Mode (Capdata team) [SQL Server]
SQL Server 2016: nouveautés intéressantes (Benjamin VESAN) [SQL Server]
Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
SQL Server Managed Instance dans Azure et le SQL Agent : pareil que du On-Prem ? (Capdata team) [AzureSQL Server]
Les Managed Service Account (MSA et gMSA) : se simplifier la vie pour gérer ses comptes de service SQL Server (Capdata team) [Operating SystemSQL Server]

Les nouveautés de SQL Server 2022