[SQL Server] Query optimization : les vertus et les méfaits du parameter sniffing

Ce billet présente le parameter sniffing (ou technique de détection de paramètres) sous SQL Server. Les indicateurs de recompilation, ainsi qu’OPTIMIZE FOR et OPTIMIZE FOR UNKNOWN seront abordés.

Vous pouvez aller ici pour comprendre certains concepts associés à l’optimisation de requêtes (i.e., compilation,…).

Le parameter sniffing : késako ?

Qu’est-ce que le parameter sniffing ?

Comme évoqué ici, l’optimiseur de requêtes utilise un plan d’exécution pour traiter une requête. Pour la construction du plan d’exécution de la requête à traiter, il jette un coup d’œil aux valeurs paramétrées. Cela lui permet de générer le plan d’exécution le plus adapté.

On parle là de parameter sniffing, qui est le comportement par défaut de SQL Server lors du traitement d’une requête.

Avantages et limites du parameter sniffing

Le fait d’effectuer un parameter sniffing permet à SQL Server de disposer d’un plan d’exécution en cache conçu en tenant compte des valeurs de paramètres traitées d’une requête donnée. Plan qui, ensuite, pourra être réutilisé en cas de réexécution de ladite requête afin de gagner du temps et d’économiser des ressources systèmes, en ignorant la phase de compilation.

Cette méthode, bien que séduisante sur le papier, a des limites. En effet, sachant que l’optimiseur de requêtes est en mesure de produire plusieurs plans d’exécution en fonction des valeurs de paramètres d’une même requête, ne se contenter que d’un seul et même plan d’exécution, peu importe les valeurs passées en paramètres de la requête, peut potentiellement avoir un impact négatif sur les performances.

Pourquoi ? Tout simplement parce qu’il peut arriver qu’une requête soit sujette à un changement fréquent de valeurs de paramètres qui peuvent rendre les statistiques de son plan d’exécution en cache inadaptées.

Par exemple, supposons qu’une procédure stockée paramétrée est chargée de traiter toutes les lignes relatives aux transactions de produits spécifiques, avec un index non-cluster sur une colonne ProductID. Si la première exécution retourne un petit nombre de produits, un opérateur Index Seek sera logiquement utilisé. En revanche, s’il y a un très grand nombre d’employés à retourner (donc, via une autre valeur de paramètre), c’est le scan d’index fera l’affaire.

En d’autres termes, le parameter sniffing n’est réellement efficace pour une requête donnée que si cette dernière a des valeurs de paramètres qui changent peu ou prou.

Mise-en-pratique des limites du parameter sniffing

Pour notre test, utilisons la base de données AdventureWorks (récupérable ici), et plus spécifiquement la table Production.TransactionHistory, puis créons une procédure stockée usp_TransactionHistory ayant pour but de lister les transactions réalisées, en fonction d’un identifiant de produit spécifique :

CREATE PROCEDURE usp_TransactionHistory @ProductID INT
AS
BEGIN
     SELECT ProductID, ReferenceOrderID, TransactionDate, Quantity
     FROM Production.TransactionHistory
     WHERE ProductID=@ProductID
END
GO

Si on exécute la procédure stockée (après avoir activé l’affichage du détail des statistiques I/O, via SET STATISTICS IO ON) :

SET STATISTICS IO ON
GO
EXEC usp_TransactionHistory @ProductID=2
GO

… le plan d’exécution sera le suivant :


… et le détail des statistiques I/O :

(45 row(s) affected)
Table ‘TransactionHistory’. Scan count 1, logical reads 148, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

L’Index Seek (et le Key Lookup, qui peut être supprimé avec l’ajout d’un index couvrant ou INCLUDE) a été logiquement privilégié pour traiter une petite quantité de données (45 lignes), avec 148 lectures logiques.

Réexécutons notre procédure stockée avec une autre valeur paramétrée :

EXEC usp_TransactionHistory @ProductID=904
GO

Le plan d’exécution est le même, mais cependant…

… le détail des statistiques I/O diffère :

(317 row(s) affected)

Table ‘TransactionHistory’. Scan count 1, logical reads 982, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

982 lectures logiques contre 148, d’où des temps I/O plus longs pour traiter 317 enregistrements avec un plan d’exécution seulement optimize pour… 45 lignes. Les joies du parameter sniffing, n’est-ce pas ?

Maintenant, vidons le cache des procédures (pour forcer une nouvelle compilation, afin de mettre en pratique la possibilité utilisation d’un autre plan d’exécution), et réexécutons notre procédure stockée comme pour la seconde fois :

DBCC FREEPROCCACHE
GO
EXEC usp_TransactionHistory @ProductID=904
GO

… le plan d’exécution sera le suivant :


… et le détail des statistiques I/O :

(317 row(s) affected)

Table ‘TransactionHistory’. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Comme on peut le souligner, le plan d’exécution utilisé diffère complètement de nos premiers tests, et les temps I/O sont devenus plus intéressants pour la valeur paramétrée « 904 ». Ce que l’on peut retenir, c’est que le parameter sniffing peut conduire à l’utilisation d’un plan d’exécution peu optimal si, en cas de réexécution, une valeur paramétrée induit l’affichage d’un jeu de résultats trop différent de la première exécution.

Solutions contre le parameter sniffing

Outre l’utilisation de commandes DBCC pour le vidage de caches (pas recommandé en production, d’autres requêtes critiques pouvant avoir besoin de leur plan en cache) comme DBCC FREEPROCCACHE, il existe diverses manières de faire face au parameter sniffing :

  • Utilisation de variables locales (ici, @local_ProductID) :
ALTER PROCEDURE usp_TransactionHistory @ProductID INT</span>
AS
BEGIN
     DECLARE @local_ProductID INT
     SET @local_ProductID=@ProductID
     SELECT ProductID, ReferenceOrderID, TransactionDate, Quantity
     FROM Production.TransactionHistory
     WHERE ProductID=@ProductID
END
GO

L’utilisation de variables locales force l’optimiseur à générer un plan d’exécution basé sur les statistiques disponibles, sans tenir compte des valeurs paramétrées.

  • Utilisation de l’indicateur OPTIMIZE FOR (à partir de SQL Server 2005) :
ALTER PROCEDURE usp_TransactionHistory
@ProductID INT
AS
BEGIN
     SELECT ProductID, ReferenceOrderID, TransactionDate, Quantity
     FROM Production.TransactionHistory
     WHERE ProductID=@ProductID
     OPTION (OPTIMIZE FOR (@ProductID = 904))
END
GO

L’option OPTIMIZE FOR permet de spécifier une constante qui sera utilisée pour optimiser le plan d’exécution de la requête. Cela peut être utile, et même très utile, quand une valeur particulière est fréquemment utilisée pour invoquer la procédure stockée. En revanche, toute autre valeur non-concernée par l’option d’optimisation n’empêchera pas la requête d’être sujette à des problèmes de performances.

  • Utilisation de l’indicateur OPTIMIZE FOR UNKNOWN (à partir de SQL Server 2008) :
ALTER PROCEDURE usp_TransactionHistory
@ProductID INT
AS
BEGIN
     SELECT ProductID, ReferenceOrderID, TransactionDate, Quantity
     FROM Production.TransactionHistory
     WHERE ProductID=@ProductID
     OPTION (OPTIMIZE FOR (@ProductID UNKNOWN))
END
GO

L’indicateur OPTIMIZE FOR UNKNOWN permet de forcer l’optimiseur à utiliser les algorithmes standard pour générer une plan d’exécution, comme s’il ne s’agissait pas d’une requête paramétrée. Le but, pour l’optimiseur, est de construire un plan d’exécution optimal sans se soucier des valeurs passées en paramètre pour une requête donnée, en se concentrant sur les statistiques.

Son fonctionnement est comparable à l’utilisation de variables locales.

  • Utilisation de l’option WITH RECOMPILE :
EXEC usp_TransactionHistory @ProductID=904
WITH RECOMPILE
GO
  • Utilisation de l’indicateur RECOMPILE :
ALTER PROCEDURE usp_TransactionHistory @ProductID INT
AS
BEGIN
     SELECT ProductID, ReferenceOrderID, TransactionDate, Quantity
     FROM Production.TransactionHistory
     WHERE ProductID=@ProductID
     OPTION (RECOMPILE)
END
GO

Les indicateurs RECOMPILE et WITH RECOMPILE peuvent être utilisés si aucune solution anti-parameter sniffing n’est satisfaisante, à condition bien sûr que les temps de (re)compilation n’impactent pas trop les temps de réponse de la requête concernée.

Et si le code source de la requête était inaccessible ou inchangeable ? Pas d’inquiétude, il est également possible d’optimiser une requête sans avoir à toucher directement à son code via l’utilisation de ce qu’on appelle un plan guide ou repère de plan (à partir de SQL Server 2005) :

EXEC sp_create_plan_guide
     @name=N'PlanGuideAntiParameterSniffing',
     @stmt=N'SELECT ProductID, ReferenceOrderID, TransactionDate, Quantity
             FROM Production.TransactionHistory
             WHERE ProductID = @ProductID',
     @type=N'OBJECT',
     @module_or_batch=N'usp_TransactionHistory',
     @params=NULL,
     @hints=N'OPTION (RECOMPILE)'
GO

Le plan guide permet donc d’associer des indicateurs spécifiques à une requête. Ci-dessous, on procède à l’association de l’option RECOMPILE à la requête usp_TransactionHistory inaccessible.

En conclusion…

Ce billet a permis de découvrir le parameter sniffing. Malgré ses inconvénients, il est important de noter qu’il n’est fondamentalement pas mauvais dans la mesure où l’objectif de l’optimiseur de requêtes est de réduire les temps de réponse d’une requête en forçant la réutilisation d’un plan d’exécution en cache, peu importe ses valeurs paramétrées. D’où le fait que SQL Server l’utilise par défaut…

Toutefois, si le parameter sniffing impacte fréquemment les temps de réponse d’une requête acceptant régulièrement des valeurs paramétrées différentes, des solutions alternatives peuvent être envisagées comme la recompilation ou OPTIMIZE FOR (UNKNOW), à moins de prévoir une réécriture plus efficace de la requête (en plusieurs batches spécifiques, par exemple).

Publicités

Un commentaire sur “[SQL Server] Query optimization : les vertus et les méfaits du parameter sniffing

  1. SQLhttp://sqlpro.developpez.com/pro dit :

    Bonjour,

    excellent article que je vais m’empresser de référencer dans mes audits… J’avais penser à en écrire un sur ce sujet en compilant l’article écrit par Erland Sommarskog.:
    http://www.sommarskog.se/query-plan-mysteries.html
    je référence ton article dans mon entré de blog : « Le mystère des plans de requêtes…. »
    http://blog.developpez.com/sqlpro/p9760/ms-sql-server/le_mystere_des_plans_de_requetes

    A +

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s