SQL Server 2025 : les nouveautés du moteur pour un DBA, mesurées

OPPO, compression ZSTD, ABORT_QUERY_EXECUTION, gouvernance de tempdb, type json et expressions régulières de SQL Server 2025, mesurés : ce qui sert en production.

Les mesures

Les essais ont tourné sur un conteneur Linux SQL Server 2025 CU7 (17.0.4065.4), édition Enterprise Developer, 22 processeurs logiques, avec des tables générées pour l’occasion (une table d’annonces d’un million de lignes) et une copie de la base de démonstration PachaDataFormation pour les sauvegardes. Les durées viennent d’une machine qui faisait autre chose en même temps : seuls les écarts nets sont retenus.

D’autres nouveautés ont leur article : l’optimized locking, la récupération accélérée, et ce que 2025 change pour l’édition Standard.

L’optimisation des paramètres optionnels (OPPO)

Le motif est connu : une procédure de recherche où chaque critère est facultatif.

USE [PachaDataFormation];
GO
CREATE OR ALTER PROCEDURE Contact.ChercherContact
    @Nom varchar(50) = NULL,
    @SocieteId int = NULL
AS
SELECT ContactId, Nom, Prenom, SocieteId
FROM Contact.Contact
WHERE (Nom = @Nom OR @Nom IS NULL)
  AND (SocieteId = @SocieteId OR @SocieteId IS NULL);

Jusqu’à la compatibilité 160, un seul plan sert tous les appels, et il doit rester valide quand le paramètre vaut NULL : pas de recherche dans l’index sur la colonne filtrée. La page Optional parameter plan optimization décrit la réponse de 2025 : un plan répartiteur (dispatcher) et des variantes, une par état du paramètre, NULL ou non. Il faut le niveau de compatibilité 170, et l’option de base OPTIONAL_PARAMETER_OPTIMIZATION, active par défaut. L’exemple ci-dessus ne règle pas la compatibilité : la base doit déjà y être.

La mesure a porté sur une procédure de même forme, dbo.Chercher (paramètres @chambres et @ville), sur une table générée d’un million de lignes indexée sur les deux colonnes :

AppelCompatibilité 160Compatibilité 170
@chambres = 10 (une ligne sur mille)parcours de l’index, 2 856 lectures de recherchevariante 1, recherche dans l’index, 7 + 2 858 lectures
sans critèremême plan, 2 874 894 lectures de recherchevariante 2, parcours de la table, 28 802 lectures

Le gain est réel, d’un facteur cent sur l’appel sans critère. Mais les paramètres sur lesquels le plan répartiteur porte dépendent du premier appel compilé :

  • compilée d’abord pour @chambres = 10, la procédure répartit sur @chambres : l’appel @ville = 7 tombe, comme l’appel sans critère, dans la variante du parcours complet, au lieu d’une recherche dans l’index sur la ville ;

  • compilée d’abord pour @ville = 7, ou pour les deux paramètres, elle répartit sur @ville : la variante « ville renseignée » fait une recherche dans l’index, et la variante « ville absente » est compilée au premier appel suivant, pour @chambres = 10. L’appel sans aucun critère retombe dans cette variante, et refait 2 874 894 lectures, comme en compatibilité 160.

  • compilée d’abord sans aucun critère, elle répartit sur les deux paramètres, avec quatre variantes : recherche dans l’index sur la ville, sur le nombre de chambres, sur les deux, et parcours de la table (28 620 lectures).

Un premier appel @chambres = 2, valeur fréquente, retient aussi @ville seul. La règle qui choisit les prédicats n’est pas documentée ; la documentation montre un exemple à trois prédicats retenus. L’événement étendu query_with_optional_parameter_predicate donne leur nombre (optional_parameter_predicate_count) et leur « asymétrie » (Skewness), sans les nommer : leurs noms se lisent dans le plan, élément Dispatcher/OptionalParameterPredicate.

En production, OPPO aide une procédure à critères facultatifs, mais son plan dépend de l’appel qui le compile, à chaque recompilation : une procédure à plusieurs critères peut garder le bon plan pour un critère et le parcours complet pour un autre. Tant que le plan obtenu n’est pas vérifié sur ses propres procédures, la réécriture classique, en SQL dynamique paramétré qui n’écrit que les critères fournis, ou avec OPTION (RECOMPILE), reste la valeur sûre.

La compression des sauvegardes ZSTD

BACKUP ... WITH COMPRESSION (ALGORITHM = ZSTD) ajoute un algorithme à MS_XPRESS et à QAT_DEFLATE (l’accélération matérielle Intel QAT, depuis 2022), avec trois niveaux. Sur une copie de PachaDataFormation (187 Mo de sauvegarde non compressée), trois essais par option :

OptionTailleDurée
sans compression187,1 Mo0,34 à 0,38 s
COMPRESSION (MS_XPRESS)91,1 Mo1,07 à 1,09 s, 1,67 s au premier essai
ZSTD, LEVEL = LOW105,8 Mo0,57 s, 0,89 s au premier essai
ZSTD, LEVEL = MEDIUM79,3 Mo0,98 à 1,00 s, 1,08 s au premier essai
ZSTD, LEVEL = HIGH76,6 Mo2,30 à 2,38 s

MEDIUM fait 13 % plus petit que MS_XPRESS pour le même temps ; LOW va deux fois plus vite pour une sauvegarde plus grosse. Microsoft présente ZSTD comme plus rapide et plus efficace que MS_XPRESS ; sur cette base, c’est l’un ou l’autre selon le niveau. Sur 187 Mo, l’écart se compte en dixièmes de seconde : il se mesure sur la vraie base avant de changer les travaux de sauvegarde. L’option de serveur backup compression algorithm, à 3, fait de ZSTD l’algorithme par défaut des sauvegardes compressées ; une sauvegarde sans COMPRESSION reste non compressée tant que backup compression default est à 0. La page des problèmes connus de 2025 signale que sp_configure refusait cette valeur ; en CU7, elle est acceptée.

ABORT_QUERY_EXECUTION

Un indicateur du Query Store, posé par sys.sp_query_store_set_hints, qui interdit l’exécution d’une requête, décrit dans le guide des bonnes pratiques des indicateurs du Query Store :

EXEC sys.sp_query_store_set_hints
     @query_id = 15,
     @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

Remplacez 15 par le query_id de la requête à bloquer, lu dans sys.query_store_query. L’exécution suivante échoue :

Msg 8778, Level 16, State 1
Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.

Le blocage porte sur le query_id, donc sur le texte exact de la requête, mais aussi sur ses options SET et sur l’objet qui la contient. Ont été exécutées sans erreur : la même requête avec une autre valeur littérale (450001 au lieu de 450000), avec deux espaces de plus, et avec OPTION (MAXDOP 1) ajoutée. Pour une requête paramétrée ou une procédure, dont le texte ne change pas, le blocage tient ; pour une requête ad hoc qui embarque ses valeurs, il ne bloque que la valeur déjà vue. Une base en PARAMETERIZATION FORCED change ce dernier cas : le query_id paramétré bloque toutes les valeurs. Sous OPPO, chaque variante a son propre query_id, et bloquer une variante n’arrête que les appels qui y tombent ; c’est le parent_query_id de sys.query_store_query_variant qu’il faut bloquer. Le Query Store compte les tentatives bloquées dans sys.query_store_runtime_stats, execution_type_desc à Exception. Le guide des bonnes pratiques donne la requête qui liste les requêtes bloquées, et une procédure d’exemple qui bloque tous les query_id d’un même query_hash. Les indicateurs du Query Store ne s’appliquent pas aux requêtes éligibles au paramétrage simple.

En production, c’est un frein d’urgence contre un rapport ou un outil qui écrase le serveur, en attendant qu’on le corrige à la source. Il n’arrête pas une exécution déjà en cours : celle-là relève de KILL.

La gouvernance de tempdb

Le Resource Governor limite en 2025 l’espace de tempdb d’un groupe de charge, en mégaoctets (GROUP_MAX_TEMPDB_DATA_MB) ou en pourcentage (GROUP_MAX_TEMPDB_DATA_PERCENT), d’après la page Gouvernance des ressources de l’espace tempdb. Avec un groupe limité à 50 Mo et une fonction de classification qui y envoie un login de test :

  • une table temporaire de 500 000 lignes s’arrête à 50 Mo ;
  • un tri d’un million de lignes, à qui l’indicateur MAX_GRANT_PERCENT = 0.1 laisse trop peu de mémoire, déborde dans tempdb et s’arrête de la même façon.
Msg 1138, Level 17, State 1
Could not allocate a new page for database 'tempdb' because that would exceed the limit
set for workload group 'g_tmp', group_id 256.

La limite porte donc sur les objets internes (débordements de tris et de hachages) comme sur les tables temporaires. La vue sys.dm_resource_governor_workload_groups compte les dépassements (total_tempdb_data_limit_violation_count) et l’occupation maximale. La documentation dit que le compteur augmente de un par requête interrompue. Mesuré après une remise à zéro : +1 en MAXDOP 1, +4 en MAXDOP 4, +20 pour le SELECT ... INTO en plan parallèle. Le compteur suit le degré de parallélisme ; la cause exacte de l’écart avec la documentation n’a pas été vérifiée. L’erreur 8657 ne l’incrémente pas.

Un premier essai du tri avec MAX_GRANT_PERCENT = 0 a échoué sur l’allocation de mémoire (erreur 8657), avant tout débordement. Cette erreur vient de l’indicateur dans un groupe de charge utilisateur, pas de la limite de tempdb : dans le groupe default, la même requête s’exécute.

En production, c’est la protection qui manquait aux instances partagées entre plusieurs applications : une requête mal écrite ne remplit plus les fichiers de données de tempdb au-delà du quota de son groupe. Le journal de tempdb et le volume qui le porte ne sont pas protégés. Microsoft déconseille de limiter le groupe default sans fonction de classification, et une limite basse fait échouer des requêtes ordinaires : la valeur se règle sur l’occupation mesurée de chaque application. La banque des versions n’est pas comptée dans la limite. Une limite en pourcentage ne s’applique que si tous les fichiers de données de tempdb ont une taille maximale (MAXSIZE), ou tous une croissance nulle ; sinon, la reconfiguration réussit avec l’avertissement 10989, et aucune limite ne joue.

Le type json

Le type json stocke les documents dans un format binaire. Mesuré sur 200 000 documents de commande d’environ 170 caractères :

TypeOctets par documentDonnéesAprès compression de pageJSON_VALUE sur 200 000 lignes, processeur
nvarchar(max)34170,9 Mo70,5 Mo1,06 à 1,08 s
varchar(max)17036,7 Mo35,9 Mo1,72 à 1,98 s
json30262,5 Mo54,0 Mo1,15 à 1,21 s

Le type json occupe un peu moins que nvarchar, beaucoup plus que varchar, et se compresse mieux que les deux. L’extraction d’une valeur n’y est pas plus rapide qu’en nvarchar sur ces documents courts. Ce qu’il apporte sûrement, c’est la validation : un document mal formé est refusé à l’insertion (erreur 13609, « JSON text is not properly formatted »), là où nvarchar l’accepte sans rien dire. La méthode modify, encore en préversion, modifie certaines valeurs en place, dans les conditions que Microsoft décrit.

En production, une colonne json peut remplacer une colonne nvarchar(max) assortie d’une contrainte CHECK (ISJSON(...) = 1), après vérification des tailles et de la structure des documents (128 niveaux au plus), et des conversions faites par l’application : aucune conversion implicite n’est permise depuis le type json. Pour la performance, rien ne justifie une migration sans mesure sur ses propres documents.

Les expressions régulières

REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR et les autres fonctions d’expressions régulières, fondées sur la bibliothèque RE2. REGEXP_LIKE exige le niveau de compatibilité 170 : en 160, elle n’existe pas (erreur 195). Les fonctions de table REGEXP_MATCHES et REGEXP_SPLIT_TO_TABLE aussi ; les autres fonctions scalaires marchent à tous les niveaux.

Sur une table d’un million de références, colonne varchar en collation SQL_Latin1_General_CP1_CI_AS, avec un index sur la colonne :

PrédicatPlanProcesseur
ref LIKE 'REF-123-%'recherche dans l’index, 6 lectures
REGEXP_LIKE(ref, '^REF-123-')parcours complet
ref LIKE '%-X'parcours complet0,61 à 0,64 s
REGEXP_LIKE(ref, '-X$')parcours complet3,17 à 3,19 s

Une expression régulière ne permet pas de recherche dans l’index, même ancrée en début de chaîne : au mieux, l’index est parcouru en entier. Le facteur cinq sur le processeur vient du LIKE, rapide sur du varchar en collation SQL. Sur la même colonne, un LIKE '%-X' en collation French_CI_AS a pris de 2,9 à 4,3 s, et en nvarchar de 3,8 à 4,6 s : autant que REGEXP_LIKE, qui ne dépend pas de la collation (3,2 s).

Elle ignore aussi la collation, ce que la page de Microsoft signale. Dans une base en collation insensible à la casse :

ExpressionRésultat
'ABC' LIKE 'abc'vrai
REGEXP_LIKE('ABC', 'abc')faux
REGEXP_LIKE('ABC', 'abc', 'i')vrai
N'Élodie' LIKE N'elodie%' en French_CI_AIvrai
REGEXP_LIKE(N'Élodie', '^elodie', 'i'), même en French_CI_AIfaux

L’option 'i' rattrape la casse ; aucune option ne reproduit l’insensibilité aux accents, il faut écrire les variantes dans le motif ('^[ÉE]lodie'). Remplacer un LIKE par une expression régulière change donc les résultats, sans erreur, dès que la casse diffère, ou les accents sous une collation insensible aux accents.

En production, les expressions régulières servent à valider ou à nettoyer des données (un format de code, d’adresse, de téléphone), où LIKE ne suffit pas. Elles ne remplacent pas LIKE dans une requête qui doit chercher dans un index.