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é[^1].
[^1] : La cardinalité est un terme de la théorie des ensembles, qui indique le nombre d’éléments dans un ensemble.
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 :
- les statistiques. Elles doivent être recalculées régulièrement ;
- 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é complètement remises à jour en SQL Server 2014. 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. Si vous conservez un niveau de compatibilité inférieur à 120, l’ancien CE sera utilisé dans tous les plans d’exécution générés dans cette base, mais vous perdrez les autres améliorations du moteur d’optimisation effectuées dans les versions suivantes.
Le code suivant affiche le nivau 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’esrimation de cardinalité (legacy cardinality estimation), à l’aide de l’option de configuration suivante, dans la base de données.
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = On;
Pour vérifier si cette option est activée :
SELECT *
FROM sys.database_scoped_configurations dsc
WHERE dsc.name = N'LEGACY_CARDINALITY_ESTIMATION';