Datafly.pro

Comme vous le savez sûrement déjà, SQL Server s'appuie sur les statistiques pour estimer le nombre de lignes (les cardinalités) qui seront retournées par chaque opération. Cette estimation va lui permettre de bâtir un plan de requête efficace et d'allouer les ressources nécessaires à son exécution.

Une mauvaise estimation est donc potentiellement la source d'une mauvaise performance pour la requête (vous pouvez relire à ce propos l'article qui vous explique ce qu'il en est avec des jointures successives http://blog.datafly.pro/post/optimiseur_jointures_et_cardinalites)

Utilisons comme exemple une table "Clients" avec un peu plus d'un million de lignes (1018774 lignes pour être exact).

Une requête simple

Pour peu que les statistiques soient à jour et avec une distribution de données homogène, l'estimation des cardinalités peut être très précise avec une requête toute simple. Interrogeons par exemple la liste de nos clients de Roubaix :

A l'aide de ses statistiques l'optimiseur estime que nous avons à peu près 2701,69 clients à Roubaix. En réalité il en existe 2873, l'estimation est donc exacte à 94 %, ce qui n'est pas mal !...

Il en sera exactement de même (2701,69 pour 2873) en interrogeant le code postal de Roubaix : nos client de Roubaix ont bien un code postal 59100, et réciproquement.

Et cette estimation est la même, que l'on soit en SQL Server 2012 ou 2014...

Filtre sur 2 colonnes (SQL 2012)

Compliquons un peu le tableau. Interrogeons notre table des clients sur la ville de Roubaix et le code postal de Roubaix. Nous savons que le résultat sera exactement des 2873 lignes que nous connaissons déjà et nous nous lançons, pleins de confiance en SQL Server...

Mais ici, l'optimiseur (celui de SQL Server 2012), n'estime que 7 lignes au lieu de 2873, l'erreur est conséquente !

L'optimiseur utilise bien la statistique de chaque colonne (chaque statistique est mono-colonne), mais il considère qu'il n'existe aucune corrélation entre la ville et le code postal. Sa formule de calcul est la suivante :

(Nb de ligne estimées pour Roubaix x Nb de lignes estimées pour 59100) / Nb de lignes total de la table

Soit : (2701.62*2701.62)/1018774 = 7.16424901, CQFD !

Comment SQL 2014 traite-t-il ce filtre sur 2 colonnes ?

Rien de bien sorcier ni d'imprévisible : dans SQL 2014, Microsoft a jugé qu'il était plus exact d'introduire "un certain degré" de corrélation entre colonnes dans sa formule de calcul. Cette nouvelle formule est la suivante :

(Nb de lignes estimées pour Roubaix / Nb de lignes total) x Racine (Nb de lignes estimées pour 59100 / Nb de lignes total) x Nb de lignes total

Soit : (2701.62/1018774) x RACINE(2701.62/1018774) x 1018774 = 139.12253

139 lignes sont un peu mieux que 7 lignes, mais nous sommes tout de même très loin du compte, car dans notre exemple la ville et le code postal corrélés à 100 %...

Est-ce toujours mieux estimé avec SQL 2014 ?

Sûrement pas : avec d'autres exemples, le mode de calcul de SQL 2012 peut se révéler bien plus exact. Prenons par exemple la requête suivante, où il n'y a pas de corrélation entre les colonnes :

Cette requête renvoie en réalité 437 lignes. Ici, SQL Server 2012 estime 435 lignes et SQL 2014 estime 1084 lignes !


Sachez enfin, si vous êtes sur SQL Server 2014, qu'il est très facile de passer d'un mode de calcul à l'autre :

• Une base en mode de compatibilité 2012 (110) ou antérieur utilisera le mode de calcul des versions précédentes. Le passage d'une base d'un mode de compatibilité à l'autre s'effectue instantanément et sans redémarrage, avec la commande : ALTER DATABASE mabase SET COMPATIBILITY_LEVEL = 90|100|110|120
• Il est possible d'activer le mode de calcul des versions précédentes par requête, avec l'option de requête OPTION (QUERYTRACEON 9481)

Je vous souhaite les meilleurs estimations...!