Migration de plans de requête

Dans la plupart des cas, la mise à niveau d'une base de données vers SQL Server 2008 améliore les performances des requêtes. Toutefois, si vous avez des requêtes critiques réglées avec soin pour la performance, vous pouvez conserver les plans de requête pour ces requêtes avant d'effectuer la mise à niveau en créant un repère de plan pour chaque requête. Si, après avoir effectué la mise à niveau, l'optimiseur de requête choisit un plan moins efficace pour l'une ou plusieurs des 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 d'effectuer la mise à niveau, procédez comme suit :

  1. Enregistrez le plan actuel pour chaque requête critique en utilisant la procédure stockée sp_create_plan_guide et en spécifiant le plan de requête dans l'indicateur de requête USE PLAN.

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

  3. Procédez à la mise à niveau de la base de données vers SQL Server 2008.

    Les plans sont conservés dans la base de données mise à niveau, dans les repères de plan et servent de secours en cas de régressions de plan 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 passer à côté d'opportunités de meilleurs plans dans la nouvelle version ou de recompilations bénéfiques en raison de statistiques mises à jour.

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

Exemple

L'exemple suivant indique comment enregistrer un plan de pré-mise à niveau pour une requête en créant un repère 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 XML peuvent être générés des manières suivantes :

Pour plus d'informations sur la génération et l'analyse des plans de requête, consultez Analyse d'une requête.

L'exemple suivant collecte le plan de requête pour 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 repère de plan nécessaire à l'application forcée du plan

À l'aide du plan de requête au format XML (obtenu à l'aide de n'importe laquelle des méthodes décrites précédemment) dans le repère de plan, copiez et collez le plan de requête en tant que littéral de chaîne à l'intérieur de 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, échappez les guillemets (') qui apparaissent dans le plan en ajoutant un deuxième guillemet avant de créer le repère de plan. Par exemple, si un plan contient WHERE A.varchar = 'This is a string', vous devez utiliser un caractère d'échappement en modifiant le code par WHERE A.varchar = ''This is a string''.

L'exemple suivant crée un repère de plan pour le plan de requête collecté lors de l'étape 1 et insère le plan d'exécution XML pour la requête dans le paramètre @hints. Pour des raisons de concision, une partie seulement de la sortie du plan d'exécution est incluse dans cet 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 produit. Vous devez constater que le plan correspond à celui que vous avez spécifié dans le repère de plan.