sp_create_plan_guide_from_handle (Transact-SQL)

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 ulteriori informazioni sulle guide di piano, vedere Informazioni sulle guide di piano.

Convenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

• [ @name = ] N'plan_guide_name'
  Nome della guida di piano. I nomi delle guide di piano sono definiti in base all'ambito del database corrente. plan_guide_name deve essere conforme alle regole per gli identificatori e non può iniziare con il simbolo di cancelletto (#). La lunghezza massima di plan_guide_name è di 124 caratteri.

• [ @plan_handle = ] plan_handle
  Identifica un batch nella cache dei piani. plan_handle è di tipo varbinary(64). È possibile ottenere plan_handle dalla vista a gestione dinamica sys.dm_exec_query_stats.

• [ @statement_start_offset = ] { statement_start_offset | NULL } ]
  Identifica la posizione iniziale dell'istruzione all'interno del batch del plan_handle specificato. statement_start_offset è di tipo int e il valore predefinito è NULL.

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

  Specificando un valore NULL o non specificando alcun offset dell'istruzione, viene creata una guida di piano per ogni istruzione nel batch utilizzando il piano di query per l'handle di piano specificato. Le guide di piano risultanti equivalgono alle guide di piano che utilizzano l'hint per la query USE PLAN per forzare l'utilizzo di un piano specifico.

Osservazioni

Non è possibile creare una guida di piano per tutti i tipi di istruzione. Se non è possibile creare una guida di piano per un'istruzione nel batch, la stored procedure ignora l'istruzione e passa all'istruzione successiva nel batch. Se un'istruzione è presente più volte nello stesso batch, viene attivato il piano per l'ultima occorrenza, disattivando 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. Si consiglia di ottenere sempre l'handle di piano dalla vista a gestione dinamica sys.dm_exec_query_stats, al fine di evitare l'insorgere dell'errore.

Nota sulla sicurezza

sp_create_plan_guide_from_handle crea guide di piano basate sui piani seguendone la visualizzazione nella cache dei piani. Ciò significa che il testo del batch, le istruzioni Transact-SQL e Showplan XML vengono acquisiti carattere per carattere (includendo qualsiasi valore letterale passato alla query) dalla cache dei piani alla guida di piano risultante. Tali stringhe di testo possono contenere informazioni riservate che vengono quindi archiviate nei metadati del database. Gli utenti con le autorizzazioni appropriate possono visualizzare queste informazioni utilizzando la vista del catalogo sys.plan_guides e la finestra di dialogo Proprietà guida di piano in SQL Server Management Studio. Per garantire che le informazioni riservate non vengano divulgate mediante una guida di piano, si consiglia di esaminare le guide di piano create dalla cache dei piani.

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

Analogamente a sp_create_plan_guide, sp_create_plan_guide_from_handle rimuove il piano di query per il batch o il modulo applicato 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. In aggiunta, sono richieste autorizzazioni singole per ogni guida di piano creata utilizzando sp_create_plan_guide_from_handle. Per creare una guida di piano di tipo OBJECT è necessario disporre dell'autorizzazione ALTER per l'oggetto a cui si fa riferimento. Per creare una guida di piano di tipo SQL o TEMPLATE è necessario disporre dell'autorizzazione ALTER 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
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 che contiene l'istruzione per la quale si sta creando la guida di piano, esaminare la colonna objtype nel set di risultati. Un valore Proc indica che la guida di piano è di tipo OBJECT. Altri valori, come ad esempio AdHoc o Prepared indicano che la guida di piano è di tipo SQL.

Esempi

A. Creazione di 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 istruzione SELECT 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 AdventureWorks2008R2;
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. Creazione di più guide di piano per un batch costituito da più istruzioni

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 da non rimuovere dalla cache dei piani il piano di query per il batch, 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. Si noti che 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 AdventureWorks2008R2;
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

Vedere anche

Riferimento

Stored procedure del Motore di database (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

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)

Concetti

Informazioni sulle guide di piano