Le nouvel estimateur de cardinalité (SQL Server 2014) est à côté de la plaque
j'ai une base de données d'entrepôt de données et je suis confronté à des problèmes avec le nouvel estimateur de cardinalité de SQL Server 2014.
après avoir mis à niveau le serveur de base de données en SQL Server 2014, j'ai observé une grande différence dans les performances de requête. Certaines requêtes s'exécutent beaucoup plus lentement (30 secondes en SQL 2012 contre 5 minutes en SQL 2014). Après des recherches sur les plans d'exécution, j'ai vu que les estimations de cardinalité sur le serveur SQL 2014 sont loin et je ne peux pas trouver une raison pour il.
voici un exemple de plan d'exécution de requête (opérateur en haut à gauche) en SQL 2012 par rapport à SQL 2014:
Quelques détails:
Mes requêtes sont des requêtes typiques de chargement de table de fait d'entrepôt de données. J'interroge une table transactionnelle et je joins beaucoup de tables (15-20) de dimensions (il y a toujours 0 ou 1 enregistrement qui est joint à partir de la table dimensionnelle).
j'ai mis à jour les statistiques de tous tableaux (avec tableau complet) pour s'assurer que les statistiques sont à jour.
les clés des tables de dimensions sont indexées (index unique non-clusted). Il me semble qu'en raison de l'unicité de cet indice l'ancien estimateur de cardinalité (SQL 2012) suppose correctement qu'il y a max. 1 Enregistrement joint (le nombre estimé d'enregistrements ne change pas dans le plan d'exécution).
j'ai essayé de réduire la question au plus simple exemple – SÉLECTIONNEZ avec 2 jointures:
Voici l'estimation de la cardinalité des opérateurs 1 et 2 en SQL 2012 par rapport à SQL 2014:
| Est.rows - SQL2012 | Est.rows - SQL2014
Operator 1 | 7653 | 7653
Operator 2 | 7653 | 10000
comme vous pouvez le voir, SQL Server 2014 manque l'estimation de plus de 30% (10000 vs. 7653). Parce que j'ai de la dpa. 15-20 rejoint dans une requête typique,l'estimation finale va loin.
je peux mettre la base de données dans le mode de compatibilité inférieure (110) et cela fonctionne très bien alors (même chose que sur SQL Server 2012), mais je voudrais vraiment savoir quelle est la raison de ce comportement. Pourquoi le résultat de l'estimateur de cardinalité de SQL Server 2014 est-il erroné?
3 réponses
je pense qu'il n'y a pas de réponse simple à cette question intéressante. La meilleure réponse que je sais, c'est la vidéo suivante: http://channel9.msdn.com/events/TechEd/NorthAmerica/2014/DBI-B331#fbid=. Il a de nombreux exemples d'anciens et de nouveaux estimateurs. La vidéo est d'environ 50+ minutes de long, mais cela en vaut la peine.
Un résumé de la vidéo, qui se rapporte à cette question:
anciennes hypothèses des estimations de la cardinalité:
- l'Uniformité – les données sont distribuées de manière uniforme.
- indépendance-la colonne 1 n'a aucun rapport avec la colonne 2.
- confinement - lorsque deux attributs peuvent être les mêmes, on présume qu'ils sont les mêmes.
- Inclusion – il devrait y avoir une correspondance.
Utilisation De SQL SERVER 2012 cardinalité de l'estimateur dans SQL SERVER 2014 utiliser l'option suivante:
- Option (querytraceon 9481) --revenir à 2012
Qu'est-ce que le nouvel estimateur (vidéo):
- SQL Server utilise la sélectivité moyenne dans l'indice et les estimations nombre de lignes en multipliant la densité de la clé du nombre total de lignes dans l'index.
- Nouvel estimateur ne fonctionne pas très bien avec des distributions.
- la plupart des différences entre les estimateurs sont basées sur la clause WHERE.
- Nouveau cardinalité estimateur estime qu'il existe une corrélation entre les tables.
- Vous pouvez créer des statistiques filtrées pour améliorer les requêtes. (http://msdn.microsoft.com/en-us/library/ms188038.aspx)
faire / liste de vérification:
1. Auto Create / Update Stats
2. Check database compatibility mode (120/110)
3. Test using query trace flags
4. XML showplan
mise à Jour Quoi de neuf dans l'estimateur de cardinalité (SQL Server 2016)
- Le plus précis.
- Le marquage CE, prédit le nombre de lignes à votre requête sera probablement de retour
- SQL Server 2016 la requête magasin
- une autre option pour suivre le les prédictions de cardinalité du CE est d'utiliser l'événement étendu nommé query_optimizer_estimate_cardinality
- CE comprend que la valeur maximale pourrait être plus élevée que lorsque les statistiques ont été recueillies pour la dernière fois
- CE comprend que les prédicats filtrés sur la même table sont souvent corrélés
- CE ne suppose plus aucune corrélation entre les prédicats filtrés des différents tableaux
Plus détails:
https://www.sqlshack.com/query-optimizer-changes-in-sql-server-2016-explained/
je me demande si vous êtes en cours d'exécution dans cette question à travers plusieurs colonnes de la sélectivité des estimations:
http://www.sqlskills.com/blogs/kimberly/multi-column-statistics-exponential-backoff/
il semble qu'il y a encore quelques bizarreries avec la nouvelle CE essayez également à l'aide de TF 4137 comme prévu et voir si cela aide.
Enfin, assurez-vous que vous êtes sur la dernière CU et sont en cours d'exécution avec TF 4199 pour couverture activer toutes les corrections d'optimiseur de requête comme toujours tester cette dans un environnement de non-production si possible d'abord et être attentif aux régressions dans d'autres requêtes quand activer les paramètres globalement
ce n'est pas une réponse directe à cette question, mais cela pourrait aider ceux qui sont confrontés à des problèmes de performance similaires liés à cette base de données SCCM (Alias ConfigMgr) concernant les changements D'estimateur de cardinalité (CE). Les requêtes SQL peuvent être interrompues ou votre console ConfigMgr peut fonctionner lentement en raison des nouvelles modifications de L'estimateur de cardinalité (CE) dans SQL Server 2014 et SQL Server 2016. Microsoft a donné une solution à ce problème ici ce qui suggère d'appliquer une cardinalité SQL appropriée Niveau de compatibilité de l'estimateur (CE) Tel qu'indiqué dans le tableau ci-dessous:
SQL Server version Supported compatibility Recommended compatibility
level values level for ConfigMgr
SQL Server 2016 130, 120, 110, 100 130
SQL Server 2014 120, 110, 100 110
espérons que cela aide!