Corriger les problèmes liés à l'estimateur de cardinalité.

Les plans d’exécutions définissent la stratégie d’exécution des requêtes, et sont bâtis par le moteur d’optimisation. Le moteur d’optimisation est basé sur le coût. Pour faire un bon travail, il doit estimer le volume de données manipulé par la requête, ce qu’on appelle l’estimation de cardinalité¹.

Il est essentiel que l’estimation de la cardinalité soit bonne. Une mauvaise estimation peut produire un mauvais plan, et de très mauvaises performances.

L’estimation de cardinalité repose sur deux piliers :

1. les statistiques. Elles doivent être recalculées régulièrement ;
2. le moteur d’estimation de cardinalité (CE pour Cardinality Estimator), qui effectue des estimations sur les cas complexes, basées sur des heuristiques (des hypothèses).

Les heuristiques du CE ont été largement implémentées en SQL Server 7, à la fin des années 90. Elles ont été revues en profondeur en SQL Server 2014 : le nouvel estimateur suppose que les valeurs de plusieurs colonnes peuvent être corrélées, et estime les jointures à partir des histogrammes des tables de base. On parle depuis de nouveau CE.

Les problèmes du nouveau CE

Le nouveau CE, depuis SQL Server 2014, améliore l’estimation de cardinalité dans de nombreux cas, mais il dégrade cette estimation dans plusieurs cas également. A la migration vers SQL Server 2014, nous avons vécu de nombreuses régressions de performances.

Si vous migrez et que vous passez le cap de SQL Server 2014, soyez très attentif à ce problème. Le nouveau CE sera utilisé si vous montez le niveau de compatibilité de votre base à SQL Server 2014 (120) ou ultérieur. La version du CE suit ensuite le niveau de compatibilité : un plan compilé en niveau 120 porte la version 120, un plan compilé en niveau 170 (SQL Server 2025) la version 170. Vous la lisez dans la propriété CardinalityEstimationModelVersion du plan d’exécution. Si vous conservez un niveau de compatibilité inférieur à 120, l’ancien CE sera utilisé par défaut pour les requêtes de cette base, mais vous perdrez les autres améliorations du moteur d’optimisation effectuées dans les versions suivantes.

Le code suivant affiche le niveau de compatibilité de vos bases de données :

SELECT d.name
      ,d.compatibility_level
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY d.name;

Résoudre les problèmes du nouveau CE

Si vous constatez des régressions de performances après mise à jour de SQL Server, vous pouvez conserver le niveau de compatibilité de la base, mais forcer l’ancien moteur d’estimation de cardinalité (legacy cardinality estimation), à l’aide de l’option de configuration suivante, dans la base de données. Cette option existe depuis SQL Server 2016. En SQL Server 2014, il faut passer par le trace flag 9481.

L’option s’applique à la base courante, d’où le USE : exécutée depuis une connexion ouverte sur master, elle modifie master, sans aucun message. L’exemple porte sur la base de démonstration PachaDataFormation.

USE [PachaDataFormation];
GO

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;

Pour vérifier si cette option est activée :

USE [PachaDataFormation];
GO

SELECT *
FROM sys.database_scoped_configurations dsc
WHERE dsc.name = N'LEGACY_CARDINALITY_ESTIMATION';

Si seules quelques requêtes régressent, vous pouvez forcer l’ancien CE requête par requête avec l’indicateur OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')), disponible depuis SQL Server 2016 SP1, ou par un Query Store hint si vous ne pouvez pas modifier le code.