SQL Server paramétrage simple et paramétrage forcé

Oguzhan Yigit
Publié par Oguzhan
Catégorie : Azure / SQL Server
11/11/2018

Dans ce post, je vais partager une optimisation SQL Server Azure que j’ai découverte en utilisant le service Advisor. Cette optimisation fonctionne cependant avec toutes les versions de SQL Server, cloud ou à demeure.

Tout a commencé lorsque, par curiosité, en consultant le service Advisor depuis le portail Azure, j’ai remarqué une recommandation Azure à propos des services que j’utilise.

azure recommendation performance

L’écran suivant montre le détail de la recommandation.

detail recommendation performance

Il s’agit d’une optimisation assez explicite et qui préconise l’utilisation de la base avec le paramètre « Forced parametization (paramétrage forcé) ».  Je vous propose de nous rafraîchir la mémoire sur ce paramètre.

 

Requêtes paramétrés & réutilisation des plans d’exécutions

A chaque fois que la base de données réceptionne une requête T-SQL, le moteur SQL Serveur travaille à l’aide de différents processus pour déterminer et retourner le résultat de la requête. Les deux principaux processus sont le “moteur relationnel” et “le moteur de stockage”.

Les processus pilotés par le moteur relationnel sont responsables de :

  • L’analyse de la requête (parsing) : cette étape permet au moteur de résoudre les objets utilisés dans la requête tels que les noms de tables ou colonnes, ou encore de résoudre les alias utilisés par exemple. Ensuite, la requête est compilée.
  • Trouver le “meilleur” plan d’exécution estimé (à l’aide de l’optimiseur de requête), c’est-à-dire le plus rapide à exécuter. L’optimisateur de requêtes évalue plusieurs paramètres comme le coût en CPU ou la lecture/écriture I/O mais il s’appuie également sur des statistiques stockées dans le moteur de base de données. Il est possible que le moteur considère que trouver le meilleur plan nécessite trop de temps de calcul. Dans ce cas de figure, SQL Server peut retourner un plan d’exécution trivial.

L’important à retenir est que le moteur propose le meilleur plan d’exécution déterminé dans un intervalle de temps minimum. Dans tous les cas, le calcul du plan d’exécution est relativement coûteux, c’est pourquoi SQL stocke ces plans, dans l’optique d’une réutilisation.

L’utilisation des paramètres dans les requêtes T-SQL augmente la probabilité qu’un plan soit réutilisé, induisant de ce fait un gain sur le temps de calcul de ce plan.

 

Configuration du paramétrage avec SQL Server

Une base de données SQL Server peut être configurée avec un “paramétrage simple” ou un “paramétrage forcé”. Le comportement du moteur est fonction de ce paramétrage. Par défaut, SQL Server est configuré pour un paramétrage simple.

 

Paramétrage simple

En cas d’exécution d’une clause SQL sans paramètre, le moteur relationnel va déterminer lui-même les paramètres à indiquer. Cela signifie que SQL Server va remplacer toutes les constantes à l’intérieur de la requête par des paramètres. L’exemple ci-dessous présente une requête, et son interprétation par SQL Server :

parametrage par sql server

L’objectif est d’augmenter la probabilité de retrouver un plan d’exécution qui correspond à la requête. Cependant, le moteur peut avoir des difficultés à transformer une requête non paramétrée en une qui l’est. Avec le mode simple, seulement un sous-ensemble de clauses peut être transformé. Il n’est pas possible de choisir les requêtes qui le seront et celles qui ne le seront pas. Si vous avez des requêtes complexes, avec un mode simple, vous devrez les paramétrer vous-mêmes. Pour plus d’informations à ce sujet, consulter l’article suivant : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186219%28v%3dsql.105%29

 

Paramétrage forcé

En précisant un paramétrage forcé, et sous certaines limitations, le moteur va paramétrer toutes les chaînes présentes dans les clauses SELECT, INSERT, UPDATE et DELETE. Le moteur aura plus de chance de réutiliser un plan d’exécution avec un paramétrage forcé. Pour plus d’informations sur le paramétrage forcé, consulter l’article suivant :
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175037(v=sql.105)

 

Pour conclure, notez qu’en fonction de vos données, modèles, requêtes, il est possible que le paramétrage forcé ne soit pas bénéfique. En effet vous pouvez remarquer des dégradations pour certaines requêtes causées par le paramétrage forcé. Dans ce cas, vous pouvez néanmoins définir des règles d’exceptions en utilisant des guides spécifiques. Suivez cet article pour plus d’informations sur les guides : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191275%28v%3dsql.105%29