Comment puis-je résoudre la dégradation des performances des procédures stockées SQL Server causée par le reniflage des paramètres ?

Dégradation des performances des procédures stockées SQL Server en raison du reniflage des paramètres

Le reniflage des paramètres est une technique d'optimisation utilisée par SQL Server pour améliorer les performances des requêtes en sélectionnant un plan d'exécution basé sur les valeurs des paramètres au moment de la première compilation de la procédure stockée. Cependant, dans certains cas, le reniflage des paramètres peut entraîner des problèmes de performances.

Considérez la procédure stockée suivante :

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

Lorsque cette procédure stockée est exécutée pour la première fois avec @MyDate comme NULL, la requête L'optimiseur génère un plan d'exécution optimisé pour cette valeur de paramètre particulière. Cependant, si la procédure stockée est ensuite exécutée avec une valeur différente pour @MyDate, l'optimiseur de requêtes risque de ne pas compiler un plan d'exécution optimal, ce qui entraînera une dégradation des performances.

Ce problème est connu sous le nom de « détection de paramètres qui a mal tourné ». . Dans l'exemple ci-dessus, même si @MyDate est en fait NULL lorsqu'il est utilisé dans la procédure stockée, le mauvais plan d'exécution généré sur la base de la compilation initiale reste en vigueur.

Pour résoudre ce problème, on peut désactiver le paramètre renifler en usurpant le paramètre :

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

Cette approche crée une copie du paramètre d'entrée et utilise cette copie pour le processus d'optimisation des requêtes, empêchant ainsi les paramètres biaisés reniflage.

Dans SQL Server 2008 et versions ultérieures, le reniflage des paramètres peut être optimisé à l'aide de l'indice OPTIMIZE FOR UNKNOWN :

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
WITH RECOMPILE, OPTIMIZE FOR UNKNOWN
AS
    -- Do Something using @MyDate

Cet indice demande à l'optimiseur de requêtes de générer un plan d'exécution basé sur des éléments inconnus. valeurs des paramètres, éliminant ainsi le risque de problèmes de performances liés au reniflage des paramètres.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!