sp_create_plan_guide_from_handle (Transact-SQL)

Article
08/19/2013

Crée un ou plusieurs repères de plan à partir d'un plan de requête dans le cache du plan. Vous pouvez appliquer cette procédure stockée pour garantir que l'optimiseur de requête utilise toujours un plan de requête spécifique pour une requête spécifiée. Pour plus d'informations sur les repères de plan, consultez Repères de plan.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'
    , [ @plan_handle = ] plan_handle
    , [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]

Arguments

[ @name = ] N'plan_guide_name'
Nom du repère de plan. Les noms des repères de plan sont limités à la base de données active. plan_guide_name doit respecter les règles relatives aux identificateurs et ne peut pas commencer par le signe #. Le paramètre plan_guide_name a une longueur maximale de 124 caractères.
[ @plan\_handle = ] plan_handle
Identifie un traitement dans le repère de plan. plan_handle est de type varbinary(64). plan_handle peut être obtenu à partir de la vue de gestion dynamique sys.dm_exec_query_stats.
[ @statement\_start\_offset = ] { statement_start_offset | NULL } ]
Identifie la position de départ de l'instruction dans le lot du plan_handle spécifié. statement_start_offset est de type int, avec NULL comme valeur par défaut.

Le décalage d'instruction correspond à la colonne statement_start_offset dans la vue de gestion dynamique sys.dm_exec_query_stats.

Lorsque la valeur NULL est spécifiée ou qu'un décalage d'instruction n'est pas spécifié, un repère de plan est créé pour chaque instruction du lot à l'aide du plan de requête pour le descripteur de plan spécifié. Les repères de plan obtenus sont équivalents à ceux qui utilisent l'indicateur de requête USE PLAN pour forcer l'utilisation d'un plan spécifique.

Notes

Un repère de plan ne peut pas être créé pour tous les types d'instructions. Si un repère de plan ne peut pas être créé pour une instruction du lot, la procédure stockée ignore l'instruction et passe à la suivante dans le lot. Si une instruction apparaît plusieurs fois dans le même lot, le plan de la dernière occurrence est activé et les plans précédents de l'instruction sont désactivés. Si aucune instruction dans le lot ne peut être utilisée dans un repère de plan, l'erreur 10532 est générée et l'instruction échoue. Nous vous recommandons de toujours obtenir le handle de plan à partir de la vue de gestion dynamique sys.dm_exec_query_stats pour empêcher toute occurrence de cette erreur.

Remarque relative à la sécurité
sp_create_plan_guide_from_handle crée des repères de plan basés sur les plans qui apparaissent dans le cache du plan. Autrement dit, le texte du lot, les instructions Transact-SQL et le plan d'exécution XML sont transférés, caractère par caractère (y compris les valeurs littérales passées à la requête), du cache du plan au repère de plan obtenu. Ces chaînes de texte peuvent contenir des informations sensibles stockées ensuite dans les métadonnées de la base de données. Les utilisateurs disposant des autorisations appropriées peuvent consulter ces informations à l'aide de l'affichage catalogue sys.plan_guides et de la boîte de dialogue Propriétés du repère de plan dans SQL Server Management Studio. Pour garantir qu'aucune information sensible n'est divulguée par le biais d'un repère de plan, nous vous recommandons d'examiner les repères de plan créés à partir du cache du plan.

sp_create_plan_guide_from_handle crée des repères de plan basés sur les plans qui apparaissent dans le cache du plan. Autrement dit, le texte du lot, les instructions Transact-SQL et le plan d'exécution XML sont transférés, caractère par caractère (y compris les valeurs littérales passées à la requête), du cache du plan au repère de plan obtenu. Ces chaînes de texte peuvent contenir des informations sensibles stockées ensuite dans les métadonnées de la base de données. Les utilisateurs disposant des autorisations appropriées peuvent consulter ces informations à l'aide de l'affichage catalogue sys.plan_guides et de la boîte de dialogue Propriétés du repère de plan dans SQL Server Management Studio. Pour garantir qu'aucune information sensible n'est divulguée par le biais d'un repère de plan, nous vous recommandons d'examiner les repères de plan créés à partir du cache du plan.

Création de repères de plan pour plusieurs instructions dans un plan de requête

Comme sp_create_plan_guide, sp_create_plan_guide_from_handle supprime du cache du plan le plan de requête du lot ou module ciblé. Cette suppression permet de garantir que tous les utilisateurs commencent à utiliser le nouveau repère de plan. Lorsque vous créez un repère de plan pour plusieurs instructions dans un plan de requête unique, vous pouvez différer la suppression du plan du cache en créant tous les repères de plan dans une transaction explicite. Cette méthode permet au plan de rester dans le cache jusqu'à ce que la transaction soit terminée et qu'un repère de plan soit créé pour chaque instruction spécifiée. Voir l'exemple B.

Autorisations

Nécessite l'autorisation VIEW SERVER STATE. De plus, des autorisations individuelles sont requises pour chaque repère de plan créé à l'aide de sp_create_plan_guide_from_handle. Pour créer un repère de plan de type OBJECT, il vous faut une autorisation ALTER sur l'objet référencé. Pour créer un repère de plan de type SQL ou TEMPLATE, il vous faut une autorisation ALTER sur la base de données active. Pour déterminer le type de repère de plan qui sera créé, exécutez la requête suivante :

SELECT cp.plan_handle, sql_handle, st.text, objtype 
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;

Dans la ligne qui contient l'instruction pour laquelle vous créez le repère de plan, examinez la colonne objtype dans le jeu de résultats. La valeur Proc indique que le repère de plan est de type OBJECT. D'autres valeurs, telles que AdHoc ou Prepared, indiquent que le repère de plan est de type SQL.

Exemples

A.Création d'un repère de plan à partir d'un plan de requête dans le cache du plan

L'exemple suivant crée un repère de plan pour une instruction SELECT unique en spécifiant un plan de requête à partir du cache du plan. L'exemple commence par exécuter une instruction SELECT simple pour laquelle le repère de plan sera créé. Le plan de cette requête est examiné à l'aide des vues de gestion dynamique sys.dm_exec_sql_text et sys.dm_exec_text_query_plan. Le repère de plan est ensuite créé pour la requête en spécifiant le plan de requête dans le cache du plan associé à la requête. La dernière instruction dans l'exemple vérifie que le repère de plan existe.

USE AdventureWorks2012;
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM Production.WorkOrder AS w 
JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE p.ProductSubcategoryID > 4
ORDER BY p.Name, DueDate;
GO
-- Inspect the query plan by using dynamic management views.
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO
-- Create a plan guide for the query by specifying the query plan in the plan cache.
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide1',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO
-- Verify that the plan guide is created.
SELECT * FROM sys.plan_guides
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO

B.Création de plusieurs repères de plan pour un lot à instructions multiples

L'exemple suivant crée un repère de plan pour deux instructions dans un lot à instructions multiples. Les repères de plan sont créés dans une transaction explicite afin que le plan de requête du lot ne soit pas supprimé du cache du plan après la création du premier repère de plan. L'exemple commence par exécuter un lot à instructions multiples. Le plan du lot est examiné à l'aide de vues de gestion dynamique. Notez qu'une ligne est retournée pour chaque instruction du lot. Un repère de plan est ensuite créé pour la première et la troisième instruction dans le lot en spécifiant le paramètre @statement\_start\_offset. La dernière instruction dans l'exemple vérifie que les repères de plan existent.

USE AdventureWorks2012;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO