Condividi tramite

sp_create_plan_guide_from_handle (Transact-SQL)

  • Articolo

Si applica a: SQL Server

Crea una o più guide di piano da un piano di query nella cache dei piani. È possibile utilizzare questa stored procedure per garantire che Query Optimizer utilizzi sempre un determinato piano di query per una query specificata. Per altre informazioni sulle guide di piano, vedere Guide di piano.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Argomenti

[ @name = ] N'name'

Nome della guida di piano. @name è sysname, senza impostazione predefinita. I nomi delle guide di piano vengono definiti a livello dell'ambito del database corrente. @name deve essere conforme alle regole per gli identificatori e non può iniziare con il segno di numero (#). La lunghezza massima di @name è di 124 caratteri.

[ @plan_handle = ] plan_handle

Identifica un batch nella cache dei piani. @plan_handle è varbinary(64), senza impostazione predefinita. @plan_handle possibile ottenere dalla vista a gestione dinamica sys.dm_exec_query_stats.

[ @statement_start_offset = ] statement_start_offset

Identifica la posizione iniziale dell'istruzione all'interno del batch del @plan_handle specificato. @statement_start_offset è int, con un valore predefinito .NULL

L'offset dell'istruzione statement_start_offset corrisponde alla colonna nella sys.dm_exec_query_stats visualizzazione a gestione dinamica.

Quando NULL viene specificato o non viene specificato un offset di istruzione, viene creata una guida di piano per ogni istruzione nel batch usando il piano di query per l'handle di piano specificato. Le guide di piano risultanti sono equivalenti alle guide di piano che usano l'hint USE PLAN per la query per forzare l'uso di un piano specifico.

Osservazioni:

Non è possibile creare una guida di piano per tutti i tipi di istruzioni. Se non è possibile creare una guida di piano per un'istruzione nel batch, la stored procedure ignora l'istruzione e continua con l'istruzione successiva nel batch. Se un'istruzione è presente più volte nello stesso batch, viene abilitato il piano per l'ultima occorrenza, disabilitando i piani precedenti per l'istruzione. Se non è possibile utilizzare alcuna istruzione nel batch in una guida di piano, viene generato l'errore 10532 e l'istruzione ha esito negativo. È consigliabile ottenere sempre l'handle di piano dalla sys.dm_exec_query_stats visualizzazione a gestione dinamica per evitare la possibilità di questo errore.

Importante

sp_create_plan_guide_from_handle crea guide di piano in base ai piani visualizzati nella cache dei piani. Ciò significa che il testo del batch, le istruzioni Transact-SQL e lo showplan XML vengono acquisiti in base al carattere (inclusi i valori letterali passati alla query) dalla cache dei piani nella guida di piano risultante. Queste stringhe di testo possono contenere informazioni riservate che vengono quindi archiviate nei metadati del database. Gli utenti con autorizzazioni appropriate possono visualizzare queste informazioni usando la sys.plan_guides vista del catalogo e la finestra di dialogo Proprietà guida piano in SQL Server Management Studio. Per assicurarsi che le informazioni riservate non vengano divulgate tramite una guida di piano, è consigliabile esaminare le guide di piano create dalla cache dei piani.

Creare guide di piano per più istruzioni all'interno di un piano di query

Come sp_create_plan_guide, sp_create_plan_guide_from_handle rimuove il piano di query per il batch o il modulo di destinazione dalla cache dei piani. Questa operazione consente a tutti gli utenti di iniziare a utilizzare la nuova guida di piano. Quando si crea una guida di piano per più istruzioni all'interno di un unico piano di query, è possibile posticipare la rimozione del piano dalla cache creando tutte le guide di piano in una transazione esplicita. Questo metodo consente al piano di rimanere nella cache fino al completamento della transazione e alla creazione di una guida di piano per ogni istruzione specificata. Vedere l'esempio B.

Autorizzazioni

È richiesta l'autorizzazione VIEW SERVER STATE. Inoltre, sono necessarie singole autorizzazioni per ogni guida di piano creata tramite sp_create_plan_guide_from_handle. La creazione di una guida di piano di tipo OBJECT richiede ALTER l'autorizzazione per l'oggetto a cui si fa riferimento. La creazione di una guida di piano di tipo SQL o TEMPLATE richiede ALTER l'autorizzazione per il database corrente. Per determinare il tipo di guida di piano che verrà creato, eseguire la query seguente:

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;

Nella riga contenente l'istruzione per cui si crea la guida di piano esaminare la objtype colonna nel set di risultati. Un valore Proc indica che la guida di piano è di tipo OBJECT. Altri valori, come ad esempio Ad hoc o Prepared indicano che la guida di piano è di tipo SQL.

Esempi

R. Creare una guida di piano da un piano di query nella cache dei piani

Nell'esempio seguente viene creata una guida di piano per una singola SELECT istruzione specificando un piano di query dalla cache dei piani. Viene innanzitutto eseguita una semplice istruzione SELECT per la quale verrà creata la guida di piano. Il piano per la query viene esaminato utilizzando le viste a gestione dinamica sys.dm_exec_sql_text e sys.dm_exec_text_query_plan. La guida di piano viene quindi creata per la query specificando il piano di query nella cache dei piani a essa associata. Nell'esempio, l'istruzione finale verifica l'esistenza della guida di piano.

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. Creare più guide di piano per un batch multistatement

Nell'esempio seguente viene creata una guida di piano per due istruzioni all'interno di un batch costituito da più istruzioni. Le guide di piano vengono create all'interno di una transazione esplicita in modo che il piano di query per il batch non venga rimosso dalla cache dei piani dopo la creazione della prima guida di piano. Viene innanzitutto eseguito un batch costituito da più istruzioni. Il piano per il batch viene esaminato utilizzando le viste a gestione dinamica. Viene restituita una riga per ogni istruzione nel batch. Viene quindi creata una guida di piano per la prima e la terza istruzione nel batch specificando il parametro @statement_start_offset. Nell'esempio, l'istruzione finale verifica l'esistenza delle guide di piano.

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

Contenuto correlato