SQL Server 2014 ne tient pas compte des statistiques multi-colonnes
Par Arian Papillon le mercredi 15 octobre 2014, 21:51 - Lien permanent
Vous avez vu dans l'article précédent le nouveau calcul de l'optimiseur pour estimer les cardinalités lorsqu'une requête comporte des prédicats sur plusieurs colonnes. Mais il y a aussi quelques limites, en particulier pour la prise en compte des statistiques multi-colonnes : l'estimateur de cardinalités 2014 est-il vraiment meilleur ?
Voyons notre requête :
SELECT * from CLIENTS WHERE Ville = 'Paris' et Region = 'IDF'
Pour cette requête, les cardinalités sont particulièrement sous-estimées, car les colonnes Ville et Region sont corrélées, ce qui peut provoquer un plan de requête inefficace et une mauvaise allocation de ressources.
- Avec SQL Server 2012, la formule de calcul qui provoquait cette
sous-estimation était :
(Nombre de lignes pour 'Paris' x Nombre de lignes pour 'IDF') / Nombre de
lignes de la table.
- SQL 2014 fait un peu mieux (mais à peine mieux) dans un cas comme celui
là. La formule utilisée est :
(Nombre de lignes pour 'Paris' x RacineCarrée(Nombre de lignes pour 'IDF'))
/ RacineCarrée(Nombre de lignes de la table)
Avec SQL 2012, une bonne solution consistait à créer des statistiques multi-colonnes, la formule de calcul utilisait dans ce cas la densité des deux colonnes combinées, ce qui permettait un calcul proche de la réalité : (Nombre de lignes pour 'IDF' * (densité ville+région / densité ville))
CREATE STATISTICS ClientsVilleRegion ON Clients (Ville,Region) WITH FULLSCAN
Mais hélas, c'est dommage, cette solution n'est plus utilisable avec
SQL Server 2014 qui ignore superbement l'existence de ces statistiques
multi-colonnes et s'en tient à son mode de calcul :
Prédicat_le_plus_sélectif * RacineCarrée(Prédicat_suivant_le plus_sélectif) *
RacineCarrée(RacineCarrée(Prédicat_suivant_le_plus_sélectif)) ...
etc...
Solutions ?
- Utiliser pour cette requête l'estimateur de cardinalité de SQL 2012 :
traceflag 9481
SELECT * from CLIENTS WHERE Ville = 'Paris' et Region = 'IDF' OPTION
(QUERYTRACEON 9481)
- Utiliser le nouveau traceflag 9471 qui force l'optimiseur a prendre en
compte la corrélation et utiliser le prédicat le plus sélectif
SELECT * from CLIENTS WHERE Ville = 'Paris' et Region = 'IDF' OPTION
(QUERYTRACEON 9471)
- Créer des statistiques filtrées si les valeurs recherchées sont connues
:
CREATE STATISTICS ClientsVille_IDF ON Clients(Ville) WHERE Region =
'IDF' WITH FULLSCAN
A vous de juger!
L'optimiseur 2014 n'est dans ce cas pas vraiment le meilleur mais surtout l'impact d'une migration de 2012 vers 2014 pourrait se révéler important, toutes les belles statistiques multi-colonnes que vous aviez ajouté ne servant plus à rien...!