SQL Server - Le plan d'exécution

Car dans le meilleur des mondes, un plan doit toujours se dérouler sans accrocs...

Les plans d'exécution sont essentiels pour optimiser et comprendre les requêtes SQL. Ils nous donnent une vision interne de la façon dont SQL Server traite les commandes, ce qui est crucial pour optimiser les performances et résoudre les problèmes potentiels. Cet article explore les concepts fondamentaux des plans d'exécution, l'usage du SQL dynamique, les procédures stockées, et fournit des insights sur les mécanismes internes de SQL Server.

Nous rentrerons dans le sujet en évoquant deux façons d'y en faire l'utilisation et en parlant de deux notions.

Le SQL Dynamique (Dynamic SQL)

Le SQL Dynamique permet d'exécuter des requêtes SQL générées dynamiquement à l'exécution. C'est particulièrement utile pour des requêtes où les critères peuvent changer fréquemment.

Exemple de base:

DECLARE @SQLCommand AS NVARCHAR(MAX);
DECLARE @City NVARCHAR(100) = 'Paris';

SET @SQLCommand = 'SELECT * FROM Customers WHERE City = ''' + @City + '''';

EXEC sp_executesql @SQLCommand;

Dans cet exemple, la ville peut être dynamiquement substituée dans la requête à l'exécution. Cependant, ce type d'implémentation peut souffrir de problèmes de performance et de sécurité, tels que l'injection SQL, si elle n'est pas correctement gérée.

La Procédure Stockée (Stored Procedures)

Les procédures stockées améliorent la sécurité et la performance en encapsulant les requêtes SQL.

Conversion du SQL Dynamique en procédure stockée:

CREATE PROCEDURE GetCustomersByCity
    @City NVARCHAR(100)
AS
BEGIN

    SELECT * FROM Customers WHERE City = @City;

END

Ici, les mêmes opérations sont effectuées de manière plus sécurisée. Les procédures stockées précompilent le plan d'exécution, ce qui améliore les performances lors d'utilisations répétées.
Les plus assidus qui grincent des dents auront notamment vu l'article sur les Indexes Couvrants. 

Les Mécanismes internes

SQL Server compile et optimise les requêtes pour créer un plan d'exécution efficace.

Les étapes clés incluent:

• Compilation
  Lors de la première exécution, SQL Server compile la requête et execute celle-ci en simultanée.
  C'est d'ailleur d'une manière grace à lui, qu'on peut retourner des erreurs, voulue ou non, dans nos scripts SQL.

• Gestion des coûts
  SQL Server utilise un modèle de coût pour déterminer le plan le plus efficace, en prenant en compte des facteurs tels que les E/S et le CPU.

• Cache, aussi nommé le Plan Cache
  Après avoir compilé notre plan d'exécution, SQL Server va stocker le plan dans le cache pour une réutilisation ultérieure.
  Cela à pour but de réduire la charge CPU pour les exécutions futures.

Un autre aspect crucial à considérer dans la gestion de SQL Server est la continuité du service. Éviter les redémarrages inutiles de SQL Server est recommandé car les plans d'exécution, stockés uniquement en mémoire (RAM), sont perdus lors de l'arrêt du système. Lorsque le serveur redémarre, ces plans d'exécution doivent être recompilés lors des premières exécutions des requêtes, ce qui peut entraîner une dégradation temporaire des performances jusqu'à ce que les plans soient de nouveau mis en cache. Ce phénomène souligne l'importance de la gestion prudente des cycles de redémarrage et de la disponibilité continue de SQL Server pour optimiser les performances et l'efficacité.

Note pour les lecteurs plus avancés:

Attention à la nuance de la réutilisation des plans, lorsque le moteur SQL réutilise un plan d'exécution, il se base sur les paramètres initiaux pour lesquels le plan a été optimisé. Cela peut introduire des problèmes dit de `Parameters Sniffings` où le plan peut ne pas être optimal pour des valeurs de paramètres différents, potentiellement affectant les performances. Cette réutilisation est bénéfique en termes de performance CPU, mais elle doit être manipulée avec prudence pour éviter des coûts imprévus.

Nous évoquerons le sujet `Parameters Sniffing` un peu plus tard..

Tuning et Optimisation des Plans d'Exécution

SQL Server inclut des outils comme le "SQL Optimizer" pour générer et analyser des plans d'exécution, permettant de comprendre les détails de l'exécution d'une requête et d'identifier les opportunités d'optimisation.

Pour le visualiser, faites executez votre requête avec l'option de généré un plan d'execution détaillé ou d'y ajouter: SET SHOWPLAN_XML ON; 

L'utilisation de "SQL Optimizer" permet de découvrir des améliorations potentielles, comme l'ajout d'index pour réduire le coût des opérations de lecture et améliorer la performance des requêtes.

Qui a dit que SQL Server n'était pas votre ami ? Avec SQL Optimizer, il est là pour s'assurer que vous obtenez les meilleures performances possibles de vos requêtes. Héhé!

Conclusion

Les plans d'exécution sont cruciaux pour le tuning de performance dans SQL Server.

Comprendre et optimiser ces plans permet d'améliorer significativement les temps de réponse des requêtes et la charge globale sur le serveur.