sp_create_plan_guide_from_handle (Transact-SQL)
S’applique à :
SQL Server
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 Plan Guides.
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 guide de plan. Les noms des repères de plan sont limités à la base de données active. plan_guide_name doivent respecter les règles relatives aux identificateurs et ne peuvent pas commencer par le signe numérique (#). La longueur maximale de plan_guide_name est de 124 caractères.
[ @plan_handle = ] plan_handle
Identifie un traitement dans le repère de plan. plan_handle est 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 int, avec la valeur null 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.
Remarques
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 descripteur de plan à partir de la vue de gestion dynamique sys.dm_exec_query_stats pour empêcher toute occurrence de cette erreur.
Important
L'instruction 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. Cela signifie que le texte de lot, les instructions Transact-SQL et le showplan XML sont pris caractère par caractère (y compris les valeurs littérales passées à la requête) du cache du plan vers le guide de plan résultant. 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 afficher ces informations à l’aide de la vue catalogue sys.plan_guides et de la boîte de dialogue Propriétés du guide 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 l'instruction sp_create_plan_guide, l'instruction 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 l'instruction sp_create_plan_guide_from_handle. Pour créer un guide de plan de type OBJECT, vous devez disposer d’une ALTER autorisation sur l’objet référencé. Pour créer un guide de plan de type SQL ou TEMPLATE, vous devez disposer d’une ALTER autorisation 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
R. 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 AdventureWorks2022;
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 AdventureWorks2022;
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
Voir aussi
Procédures stockées du moteur de base de données (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
Repères de plan
sp_create_plan_guide (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour