Partager via

sp_create_plan_guide_from_handle (Transact-SQL)

  • Article

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'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Arguments

[ @name = ] N’name'

Nom du repère de plan. @name est sysname, sans valeur par défaut. Les noms des repères de plan sont limités à la base de données active. @name devez respecter les règles relatives aux identificateurs et ne peut pas commencer par le signe numérique (#). La longueur maximale de @name est de 124 caractères.

[ @plan_handle = ] plan_handle

Identifie un traitement dans le repère de plan. @plan_handle est varbinary(64), sans valeur par défaut. @plan_handle pouvez obtenir à partir de la vue de gestion dynamique sys.dm_exec_query_stats.

[ @statement_start_offset = ] statement_start_offset

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 par défaut NULL.

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

Quand NULL un décalage d’instruction est spécifié ou 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 handle de plan spécifié. Les repères de plan résultants sont équivalents aux repères de plan qui utilisent l’indicateur USE PLAN de requête 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 dans le lot, la procédure stockée ignore l’instruction et continue à l’instruction 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 d’obtenir toujours le handle de plan à partir de la sys.dm_exec_query_stats vue de gestion dynamique pour éviter la possibilité de cette erreur.

Important

sp_create_plan_guide_from_handle crée des repères de plan basés sur des plans tels qu’ils apparaissent dans le cache du plan. Cela signifie que le texte par lots, les instructions Transact-SQL et le plan d’exécution XML sont pris en caractères par caractère (y compris les valeurs littérales passées à la requête) du cache de plan dans le repère de plan résultant. Ces chaînes de texte peuvent contenir des informations sensibles qui sont ensuite stockées 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 l’affichage sys.plan_guides catalogue et de la boîte de dialogue Propriétés du repère de plan dans SQL Server Management Studio. Pour vous assurer que les informations sensibles ne sont pas divulguées par le biais d’un guide de plan, nous vous recommandons d’examiner les repères de plan créés à partir du cache du plan.

Créer des 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 le plan de requête pour le lot ou le module ciblé du cache du plan. 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. En outre, les autorisations individuelles sont requises pour chaque repère de plan créé à l’aide sp_create_plan_guide_from_handlede . La création d’un repère de plan de type OBJECT nécessite ALTER une autorisation sur l’objet référencé. Création d’un repère de plan de type SQL ou TEMPLATE nécessite ALTER une 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
INNER 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 objtype colonne 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 Ad hoc ou Prepared, indiquent que le repère de plan est de type SQL.

Exemples

R. Créer 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 unique SELECT 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
INNER 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éer plusieurs repères de plan pour un lot à états 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. Une ligne pour chaque instruction du lot est retournée. 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

Contenu connexe