Cette nouvelle option présente dans SQL server 2017, nous permet de mettre en place des requêtes plus performantes. Pour ce faire 3 options s’offrent à nous :
- batch mode adaptive joins
- batch mode memory grant feedback
- interleaved execution for multi-statement table valued functions
Pour activer cette option ALTER DATABASE [mon_datawarehouse] SET COMPATIBILITY_LEVEL = 140;
Rappel rapide du fonctionnement d’SQL server sur l’exécution des requêtes :
- Etape 1 : Liste de divers scénarios de plan d’exécution.
- Etape 2 : estimation en coût (nb de lignes, en % CPU) de ces divers scénarios => ce coût calculé peut-être faux si les dernières statistiques ne sont pas à jour par exemple. Pour en savoir plus sur ces coûts, voici un petit article sympa
- Etape 3 : selection du scénario le plus faible en coût d’exécution
- Etape 4 : exécution de la requête en fonction de ce plan d’exécution sélectionné
Détaillons maintenant chaque option :
le batch mode adaptive joins :
SQL server utilise actuellement 3 algorithmes pour procéder à une jointure : »nested loop », « merge », et « hash match ». Pour choisir le plus approprié, SQL server se base sur les coûts estimés.
Grâce à cette nouvelle option voici comment va fonctionner SQL server 2017 : si nb lignes de l’entrée de la jointure <= au seuil défini par cette option alors « nested loop » sinon « hash match ». Cette analyse est faite dynamiquement lors de l’exécution de la requête.
Note : La jointure »hash match » est utilisée dans ce cas lorsqu’
- un index columnstore est présent dans la requête globale
- ou quand la jointure référence directement la table d’index columnstore
le batch mode memory grant feedback :
Cette option permet de calculer pour chaque requête la mémoire nécessaire à l’exécution de cette dernière et de mettre à jour le « cached plan » (l’endroit ou les plans d’exécution sont mis en cache pour réutilisation si une requête similaire venait à être exécutée). La gestion de la mémoire nécessaire à l’exécution des requêtes est mieux gérée. Une trop grande allocation mémoire bloquante pour d’autres requêtes ou une allocation mémoire trop juste ralentissant les performances de la requête ne seront plus possibles. Cette option nécessite l’utilisation des « columnstore indexes »
voici un exemple bien mené pour comprendre avec ou sans cette option
Interleaved execution for multi-statement table valued functions :
Ce genre de fonction existait déjà sur les versions précédentes SQL. Elle a été améliorée dans SQL server 2017 afin d’augmenter son efficacité. Les couts d’exécution sont calculés sur chaque sous requête de la fonction et donc propose un plan d’exécution plus précis et plus performant sur le nombre de lignes à prendre en compte et donc la mémoire à allouée.
Avez vous déjà activées et utilisées ces options?
Voici ma source de données