sp_create_plan_guide_from_handle (Transact-SQL)sp_create_plan_guide_from_handle (Transact-SQL)

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)noDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Crea una o più guide di piano da un piano di query nella cache dei piani.Creates one or more plan guides from a query plan in the plan cache. È possibile utilizzare questa stored procedure per garantire che Query Optimizer utilizzi sempre un determinato piano di query per una query specificata.You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query. Per altre informazioni sulle guide di piano, vedere Guide di piano.For more information about plan guides, see Plan Guides.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintassiSyntax


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

ArgomentiArguments

[ @name =] N'plan_guide_name'[ @name = ] N'plan_guide_name'
Nome della guida di piano.Is the name of the plan guide. I nomi delle guide di piano vengono definiti a livello dell'ambito del database corrente.Plan guide names are scoped to the current database. plan_guide_name devono essere conformi alle regole per identificatori e non può iniziare con il simbolo di cancelletto (#).plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). La lunghezza massima di plan_guide_name è 124 caratteri.The maximum length of plan_guide_name is 124 characters.

[ @plan_handle =] plan_handle[ @plan_handle = ] plan_handle
Identifica un batch nella cache dei piani.Identifies a batch in the plan cache. plan_handle è varbinary(64).plan_handle is varbinary(64). plan_handle possono essere ottenuti dal Sys.dm exec_query_stats vista a gestione dinamica.plan_handle can be obtained from the sys.dm_exec_query_stats dynamic management view.

[ @statement_start_offset =] { statement_start_offset | NULL}][ @statement_start_offset = ] { statement_start_offset | NULL } ]
Identifica la posizione iniziale dell'istruzione nel batch specificato plan_handle.Identifies the starting position of the statement within the batch of the specified plan_handle. statement_start_offset è int, con un valore predefinito è NULL.statement_start_offset is int, with a default of NULL.

L'offset dell'istruzione corrisponde alla colonna statement_start_offset nella Sys.dm exec_query_stats vista a gestione dinamica.The statement offset corresponds to the statement_start_offset column in the sys.dm_exec_query_stats dynamic management view.

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.When NULL is specified or a statement offset is not specified, a plan guide is created for each statement in the batch using the query plan for the specified plan handle. 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.The resulting plan guides are equivalent to plan guides that use the USE PLAN query hint to force the use of a specific plan.

OsservazioniRemarks

Non è possibile creare una guida di piano per tutti i tipi di istruzione.A plan guide cannot be created for all statement types. 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.If a plan guide cannot be created for a statement in the batch, the stored procedure ignores the statement and continues to the next statement in the 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.If a statement occurs multiple times in the same batch, the plan for the last occurrence is enabled and previous plans for the statement are disabled. Se non è possibile utilizzare alcuna istruzione nel batch in una guida di piano, viene generato l'errore 10532 e l'istruzione ha esito negativo.If no statements in the batch can be used in a plan guide, error 10532 is raised and the statement fails. 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.We recommend that you always obtain the plan handle from the sys.dm_exec_query_stats dynamic management view to help avoid the possibility of this error.

Importante

sp_create_plan_guide_from_handle crea guide di piano basate sui piani seguendone la visualizzazione nella cache dei piani.sp_create_plan_guide_from_handle creates plan guides based on plans as they appear in the plan cache. Ciò significa che il testo del batch, le istruzioni Transact-SQLTransact-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.This means that the batch text, Transact-SQLTransact-SQL statements, and XML Showplan are taken character-by-character (including any literal values passed to the query) from the plan cache into the resulting plan guide. Tali stringhe di testo possono contenere informazioni riservate che vengono quindi archiviate nei metadati del database.These text strings may contain sensitive information that is then stored in the metadata of the database. Gli utenti con autorizzazioni appropriate possono visualizzare queste informazioni tramite la vista del catalogo plan_guides e proprietà Guida di piano nella finestra di dialogo SQL Server Management StudioSQL Server Management Studio.Users with appropriate permissions can view this information by using the sys.plan_guides catalog view and the Plan Guide Properties dialog box in SQL Server Management StudioSQL 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.To ensure that sensitive information is not disclosed through a plan guide, we recommend reviewing the plan guides created from the plan cache.

Creazione di guide di piano per più istruzioni all'interno di un piano di queryCreating Plan Guides for Multiple Statements Within a Query Plan

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.Like sp_create_plan_guide, sp_create_plan_guide_from_handle removes the query plan for the targeted batch or module from the plan cache. Questa operazione consente a tutti gli utenti di iniziare a utilizzare la nuova guida di piano.This is done to ensure that all users begin using the new plan guide. 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.When creating a plan guide for multiple statements within a single query plan, you can postpone the removal of the plan from the cache by creating all the plan guides in an explicit transaction. 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.This method allows the plan to remain in the cache until the transaction is complete and a plan guide for each specified statement is created. Vedere l'esempio B.See Example B.

AutorizzazioniPermissions

È richiesta l'autorizzazione VIEW SERVER STATE.Requires VIEW SERVER STATE permission. In aggiunta, sono richieste autorizzazioni singole per ogni guida di piano creata utilizzando sp_create_plan_guide_from_handle.In addition, individual permissions are required for each plan guide that is created by using 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.To create a plan guide of type OBJECT requires ALTER permission on the referenced object. Per creare una guida di piano di tipo SQL o TEMPLATE è necessario disporre dell'autorizzazione ALTER per il database corrente.To create a plan guide of type SQL or TEMPLATE requires ALTER permission on the current database. Per determinare il tipo di guida di piano che verrà creato, eseguire la query seguente:To determine the plan guide type that will be created, run the following query:

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.In the row that contains the statement for which you are creating the plan guide, examine the objtype column in the result set. Un valore Proc indica che la guida di piano è di tipo OBJECT.A value of Proc indicates the plan guide is of type OBJECT. Altri valori, come ad esempio AdHoc o Prepared indicano che la guida di piano è di tipo SQL.Other values such as AdHoc or Prepared indicate the plan guide is of type SQL.

EsempiExamples

A.A. Creazione di una guida di piano da un piano di query nella cache dei pianiCreating a plan guide from a query plan in the plan cache

Nell'esempio seguente viene creata una guida di piano per una singola istruzione SELECT specificando un piano di query dalla cache dei piani.The following example creates a plan guide for a single SELECT statement by specifying a query plan from the plan cache. Viene innanzitutto eseguita una semplice istruzione SELECT per la quale verrà creata la guida di piano.The example begins by executing a simple SELECT statement for which the plan guide will be created. 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.The plan for this query is examined by using the sys.dm_exec_sql_text and sys.dm_exec_text_query_plan dynamic management views. La guida di piano viene quindi creata per la query specificando il piano di query nella cache dei piani a essa associata.The plan guide is then created for the query by specifying the query plan in the plan cache that is associated with the query. Nell'esempio, l'istruzione finale verifica l'esistenza della guida di piano.The final statement in the example verifies that the plan guide exists.

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.B. Creazione di più guide di piano per un batch costituito da più istruzioniCreating multiple plan guides for a multistatement batch

Nell'esempio seguente viene creata una guida di piano per due istruzioni all'interno di un batch costituito da più istruzioni.The following example creates a plan guide for two statements within a multistatement batch. 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.The plan guides are created within an explicit transaction so that the query plan for the batch is not removed from the plan cache after the first plan guide is created. Viene innanzitutto eseguito un batch costituito da più istruzioni.The example begins by executing a multistatement batch. Il piano per il batch viene esaminato utilizzando le viste a gestione dinamica.The plan for the batch is examined by using dynamic management views. Si noti che viene restituita una riga per ogni istruzione nel batch.Notice that a row for each statement in the batch is returned. Viene quindi creata una guida di piano per la prima e la terza istruzione nel batch specificando il parametro @statement_start_offset.A plan guide is then created for the first and third statements in the batch by specifying the @statement_start_offset parameter. Nell'esempio, l'istruzione finale verifica l'esistenza delle guide di piano.The final statement in the example verifies that the plan guides exist.

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

Vedere ancheSee Also

Motore di database Stored procedure ( Transact-SQL ) Database Engine Stored Procedures (Transact-SQL)
Sys.dm exec_query_stats ( Transact-SQL ) sys.dm_exec_query_stats (Transact-SQL)
Guide di piano Plan Guides
sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
Sys.dm exec_sql_text ( Transact-SQL ) sys.dm_exec_sql_text (Transact-SQL)
Sys.dm exec_text_query_plan ( Transact-SQL ) sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide ( Transact-SQL )sp_control_plan_guide (Transact-SQL)