Partager via

Migrer des plans de requête

Dans la plupart des cas, la mise à niveau d’une base de données vers la version la plus récente de SQL Server entraîne une amélioration des performances des requêtes. Toutefois, si vous avez des requêtes stratégiques qui ont été soigneusement ajustées pour les performances, vous pouvez conserver les plans de requête pour ces requêtes avant la mise à niveau en créant un guide de plan pour chaque requête. Si, après la mise à niveau, l’optimiseur de requête choisit un plan moins efficace pour une ou plusieurs requêtes, vous pouvez activer les repères de plan et forcer l’optimiseur de requête à utiliser les plans de pré-mise à niveau.

Pour créer des repères de plan avant la mise à niveau, procédez comme suit :

  1. Enregistrez le plan actuel pour chaque requête critique de mission à l’aide de la procédure stockée sp_create_plan_guide et spécifiez le plan de requête dans l’indicateur de requête USE PLAN.

  2. Vérifiez que le guide de plan est appliqué à la requête.

  3. Mettez à niveau la base de données vers la version la plus récente de SQL Server.

    Les plans sont conservés dans la base de données mise à niveau dans les guides de plan et servent comme solution de repli en cas de régressions des plans après la mise à niveau.

    Nous vous recommandons de ne pas activer les repères de plan après la mise à niveau, car vous risquez de manquer des opportunités pour de meilleurs plans dans la nouvelle version ou des recompilations bénéfiques en raison de statistiques mises à jour.

  4. Si les plans moins efficaces sont choisis après la mise à niveau, activez tous ou un sous-ensemble des repères de plan pour remplacer les nouveaux plans.

Exemple :

L’exemple suivant montre comment enregistrer un plan de pré-mise à niveau pour une requête en créant un guide de plan.

Étape 1 : Collecter le plan

Le plan de requête enregistré dans le repère de plan doit être au format XML. Les plans de requête au format XML peuvent être générés de différentes manières :

L’exemple suivant collecte le plan de requête de l’instruction SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; en interrogeant des vues de gestion dynamique.

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO

Étape 2 : Créer le guide de plan pour forcer le plan

À l’aide du plan de requête au format XML (obtenu par l’une des méthodes précédemment décrites) dans le guide de planification, copiez et collez le plan de requête en tant que chaîne de caractères dans l’indicateur de requête USE PLAN spécifié dans la clause OPTION de sp_create_plan_guide.

Dans le plan XML lui-même, les guillemets (') qui apparaissent dans le plan doivent être échappés par un double guillemet avant de créer le guide du plan. Par exemple, un plan qui contient WHERE A.varchar = 'This is a string' doit être échappé en modifiant le code pour WHERE A.varchar = ''This is a string''.

L'exemple suivant crée un guide de plan pour le plan de requête collecté à l'étape 1 et insère le Showplan XML de la requête dans le paramètre @hints. Pour des raisons de concision, seule la sortie partielle de Showplan est incluse dans l’exemple.

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO

Étape 3 : Vérifier que le repère de plan est appliqué à la requête

Réexécutez la requête et examinez le plan de requête généré. Vous devez voir que le plan correspond à celui que vous avez spécifié dans le guide de plan.

Voir aussi

sp_create_plan_guide (Transact-SQL)
Indicateurs de requête (Transact-SQL)
Guides de planification

  • Last updated on